Recursion in a stored procedure

Database
Enthusiast

Recursion in a stored procedure

Hello. Our organization is transitioning from an Oracle system where we had numerous stored procs that accepted a p_employee_id parameter, then recursively queried a hierarchy table to find all employees below the supplied one in the hierarchy. Essentially, the parent_id field on each employee's record was the employee_id of the parent record.

How can this be done on Teradata?

From what I've seen:
* Teradata procs do not support the "With Recursive" clause of Selects
* Teradata macros do not support variables or control structures like if/then/while/etc.
* Teradata recursive views do not support parameters

18 REPLIES
Junior Contributor

Re: Recursion in a stored procedure

Hi Jesse,

* Teradata procs do not support the "With Recursive" clause of Selects

Of course you can use WITH RECURSIVE in SPs, but only TD12 allows a SP to return a result set.

* Teradata macros do not support variables or control structures like if/then/while/etc.

Yep, but they support parameters.

* Teradata recursive views do not support parameters

Views never support parameters, but you can do a "select from recursive_view where parent_id = xxx"

Dieter
Enthusiast

Re: Recursion in a stored procedure

>>> Of course you can use WITH RECURSIVE in SPs, but only TD12 allows a SP to return a result set.

Really?

For this code:

CREATE PROCEDURE TEST_RPT_OBJECTS.usp_jw_test(

p_org_entity_id integer

)

DYNAMIC RESULT SETS 1

BEGIN

DECLARE curOutput CURSOR WITH RETURN ONLY FOR

WITH RECURSIVE temp_hdc (org_entity_id, hier_level) AS (

SELECT org_entity_id, 1 AS hier_level

FROM TEST_UNIT_INFO.HIERARCHY_DETAIL_CURRENT

WHERE org_entity_id = :p_org_entity_id

UNION ALL

SELECT HDC.org_entity_id, T.hier_level+1 AS hier_level

FROM temp_hdc T, TEST_UNIT_INFO.hierarchy_detail_current HDC

WHERE T.org_entity_id = HDC.parent_id

)

SELECT * FROM temp_hdc;

OPEN curOutput;

END;

I get this error:

SPL1027:E(L16), Missing/Invalid SQL statement'E(6926):WITH [RECURSIVE] clause or recursive view is not supported within WITH [RECURSIVE] definitions, views, triggers or stored procedures.'

>>> Views never support parameters, but you can do a "select from recursive_view where parent_id = xxx"

Doesn't that just give you one level of children? If so, how is that different from using the same clause on the original table without any recursion whatsoever?

Since we've a 1000 people in our organizational hierarchy, (unless I use a macro) it would appear Teradata wants me to create 1000 different recursive views in order that when a person logs in, they can view data for only persons below them in the hierarchy. : ( Hopefully, I'm wrong.
Junior Contributor

Re: Recursion in a stored procedure

Hi Jesse,
sorry for the missinformation, i didn't know about that restriction in SPs.

The view i mentioned needs to fully explode the hierarchy, thus you can filter for a given root.

This example uses Teradata's database hierarchy and creates data similar to dbc.children:

REPLACE RECURSIVE VIEW Database_Hierarchy (OwnerName, DatabaseName, lvl) AS
(
SELECT
OwnerName,
DatabaseName,
1 AS lvl
FROM dbc.databases
WHERE DatabaseName NOT IN ('extuser', 'dbc')

UNION ALL

SELECT
cte.OwnerName,
ma.DatabaseName,
lvl + 1 AS lvl
FROM dbc.databases AS ma JOIN Database_Hierarchy AS cte
ON cte.DatabaseName = ma.OwnerName

/* Just in case to prevent a potential endless loop */
WHERE lvl <= 6
);

SELECT * FROM Database_Hierarchy
WHERE OwnerName = 'dbc';

Dieter
Enthusiast

Re: Recursion in a stored procedure

I do not have select with grant access to DBC.databases...

Which of the following does your "SELECT * FROM Database_Hierarchy WHERE OwnerName = 'dbc'; " return?
a) direct children of 'dbc'
b) all children, grand-children, great-grand-children, etc... below 'dbc'

rgs
Enthusiast

Re: Recursion in a stored procedure

RE: * Teradata procs do not support the "With Recursive" clause of Selects

You can write a JAVA external stored procedure and submit a recursive select that way (TD12). It’s called just like any stored procedure would be. Or you can create a CLI based external stored procedure written in C/C++ and do the same thing. You can also return results sets from those procedures. That is one way to get around the issue of the SQL stored procedure not supporting the recursive select.
Junior Contributor

Re: Recursion in a stored procedure

Hi Jesse,
all children of dbc.

Dieter
Enthusiast

Re: Recursion in a stored procedure

Exciting! Thank you, Dieter. I will have to experiment with that.
Enthusiast

Re: Recursion in a stored procedure

: (
Didn't work. Tried your code with using Find/Replace to substitute my object names. It returned only the direct children -which is the same result as selecting directly from the source table with no recursion.

I guess I'm off to try some sort of while loop populating a volatile temp table...
Junior Contributor

Re: Recursion in a stored procedure

Hi Jesse,
you probably did something wrong during search & replace.

Just try the query without view:

with RECURSIVE Database_Hierarchy (OwnerName, DatabaseName, lvl) AS
(
SELECT
OwnerName,
DatabaseName,
1 AS lvl
FROM dbc.databases
WHERE DatabaseName NOT IN ('extuser', 'dbc')

UNION ALL

SELECT
cte.OwnerName,
ma.DatabaseName,
lvl + 1 AS lvl
FROM dbc.databases AS ma JOIN Database_Hierarchy AS cte
ON cte.DatabaseName = ma.OwnerName

/* Just in case to prevent a potential endless loop */
WHERE lvl <= 6
)
SELECT * FROM Database_Hierarchy
WHERE OwnerName = 'dbc';

Dieter