Recursion in a stored procedure

Database
Enthusiast

Re: Recursion in a stored procedure

As I haven't available permissions to use your table, can we create one and work from there?

CREATE SET TABLE TEST_RTIDB.hierarchy ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
parent_id INTEGER,
id INTEGER,
hier_name VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( id );

INSERT INTO TEST_RTIDB.hierarchy(null,1,'Great-Grand-Parent');
INSERT INTO TEST_RTIDB.hierarchy(1,2,'Grand-Parent');
INSERT INTO TEST_RTIDB.hierarchy(2,3,'Parent');
INSERT INTO TEST_RTIDB.hierarchy(3,4,'Child');
INSERT INTO TEST_RTIDB.hierarchy(3,5,'2nd Child');

WITH RECURSIVE temp_hierarchy(parent_id, id, hier_name, lvl) AS (
SELECT parent_id, id, hier_name, 1 as lvl
FROM TEST_RTIDB.hierarchy
WHERE parent_id IS NULL

UNION ALL
SELECT H.parent_id, H.id, H.hier_name, T.lvl+1 as lvl
FROM TEST_RTIDB.hierarchy H, temp_hierarchy T
WHERE T.id = H.parent_id
)
SELECT id, hier_name
FROM temp_hierarchy
where parent_id = 2;

/* Above SELECT shows "3 Parent" when I want it to
show:
3 Parent
4 Child
5 2nd Child
*/
Highlighted
rgs
Enthusiast

Re: Recursion in a stored procedure

Is not the where condition of your select supposed to be:

where parent_id >= 2;

Junior Contributor

Re: Recursion in a stored procedure

Hi Jesse,
your query just creates the hierarchy tree from root, whereas my query creates the Transitive Closure of that hierarchy.
It starts with all rows and then creates all possible pathes up to the top.

WITH RECURSIVE temp_hierarchy(parent_id, id, hier_name, lvl) AS (
SELECT parent_id, id, hier_name, 1 AS lvl
FROM hierarchy
/** was: WHERE parent_id IS NULL **/
WHERE parent_id IS NOT NULL
UNION ALL
/** was: SELECT H.parent_id, H.id, H.hier_name, T.lvl+1 as lvl **/
SELECT T.parent_id, H.id, H.hier_name, T.lvl + 1 AS lvl
FROM hierarchy H, temp_hierarchy T
WHERE T.id = H.parent_id
)
SELECT *
FROM temp_hierarchy
WHERE parent_id = 2
;

Run this query without WHERE-condition and you'll see all pathes.

Dieter
Enthusiast

Re: Recursion in a stored procedure

Thanks Dieter. It looks like you're getting the results I'm after except the lvl field now has strange values. I will study your "Transitive Closure" query.
Junior Contributor

Re: Recursion in a stored procedure

No, it works for any tree-like hierarchy and returns *all* children of parent_id x.

Dieter
Enthusiast

Re: Recursion in a stored procedure

Dieter. Thanks again. You're query is working well and I adapted it successfully to a recursive view. One glitch, however... The results are shown with the lvl field starting from the top going down:

hier_name lvl
Parent 1
Child 2
2nd Child 2

I need the numbering to go from the bottom up where the lowest level child is always "1"...

hier_name lvl
Parent 2
Child 1
2nd Child 1

Or
hier_name lvl
Grand Parent 3
Parent 2
Child 1
2nd Child 1

Is this possible?
Junior Contributor

Re: Recursion in a stored procedure

Hi Jesse,
i don't know how to get that within the view, but you can calculate it within the select:

WITH RECURSIVE temp_hierarchy(parent_id, id, hier_name, lvl) AS (
SELECT parent_id, id, hier_name, 1 AS lvl
FROM hierarchy
/** was: WHERE parent_id IS NULL **/
WHERE parent_id IS NOT NULL
UNION ALL
/** was: SELECT H.parent_id, H.id, H.hier_name, T.lvl+1 as lvl **/
SELECT T.parent_id, H.id, H.hier_name, T.lvl + 1 AS lvl
FROM hierarchy H, temp_hierarchy T
WHERE T.id = H.parent_id
)
SELECT hier_name, MAX(lvl) OVER (PARTITION BY parent_id) - lvl + 1
FROM temp_hierarchy
WHERE parent_id = 2

Dieter
Enthusiast

Re: Recursion in a stored procedure

Wow! You've been immensely helpful... Thanks.
Enthusiast

Re: Recursion in a stored procedure

Jesse,

You can use BTEQ statements in Macros like :

ECHO 'IF ActivityCount = 0 Then .Exit 0';

and it works fine for implementing conditional logic in Macros.

Amit