How to find the parent record using self join in N

Question

I have a table with keys (i.e just numbers) as org_key and par_org_key

Org_key | Par_Org_key
52      26
23      89
26      14
14      8

In the above tablem the parent of org_key 52 is 26, the parent of org_key 26 is 14 an so on The root parent for the above series is 8 (as it is not listed in the org_key column). I need to find such roots and have the complete link eg: 8|14|26|52 on a single row.

This SQL is working in Oracle:

select 
      substr(sys_connect_by_path(org_nbr,'|'),2)  spa
     ,substr(sys_connect_by_path(org_key,'|'),2)  org_key_line
     ,connect_by_root(org_key)                    org_key_root
     ,level                                       lvl
     ,org.*
from org
start with par_org_key is null
connect by par_org_key = prior org_key 

My question is, how I can do the same query using Netezza SQL?

Answer 1

Netezza doesn't support recursive Common Table Expressions otherwise that would be the more elegant and scalable solution. I've found the best workaround is to use Left Outer Joins to the same table.

Create a test table. We use -1 to denote the root node.

CREATE TABLE t1 AS
SELECT 52 AS Org_key, 26 AS Par_Org_key
UNION
SELECT 23 AS Org_key, 89 AS Par_Org_key
UNION
SELECT 26 AS Org_key, 14 AS Par_Org_key
UNION
SELECT 14 AS Org_key, 8 AS Par_Org_key
UNION
SELECT 8 AS Org_key, -1 AS Par_Org_key;

This returns 8|14|26|52

SELECT NVL(a.Org_key,'0') || '|' || NVL(b.Org_key,'0') || '|' || NVL(c.Org_key,'0') || '|' || NVL(d.Org_key,'0')
FROM t1 a
LEFT OUTER JOIN t1 b ON a.Org_key = b.Par_Org_key
LEFT OUTER JOIN t1 c ON b.Org_key = c.Par_Org_key
LEFT OUTER JOIN t1 d ON c.Org_key = d.Par_Org_key
LEFT OUTER JOIN t1 e ON d.Org_key = e.Par_Org_key
WHERE a.Par_Org_key = -1;

If you want to add more Left Outer Joins to support an unknown number of levels, this can help handle the nulls that result

SELECT NVL(CAST(a.Org_key AS VARCHAR(10)),'') || '|' || NVL(CAST(b.Org_key AS VARCHAR(10)),'') || '|' || NVL(CAST(c.Org_key AS VARCHAR(10)),'') || '|' || NVL(CAST(d.Org_key AS VARCHAR(10)),'')