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
>>> Of course you can use WITH RECURSIVE in SPs, but only TD12 allows a SP to return a result set.
For this code:
CREATE PROCEDURE TEST_RPT_OBJECTS.usp_jw_test(
DYNAMIC RESULT SETS 1
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
WHERE org_entity_id = :p_org_entity_id
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;
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.
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'
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.
: ( 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...