This is in Oracle SQL using their BI Publisher Data Model tool
Say you have two tables:
hierarchy_table
acct_description_table
and in these two tables you have these columns:
hierarchy_table
+------------+-----------+-------+
| primarykey | parentkey | depth |
+------------+-----------+-------+
acct_description_table
+------------+-------------+
| acct_value | description |
+------------+-------------+
and with this data you want to build out a hierarchy like so (descriptions omitted):
+----------------+----------------+----------------+
| acct_depth_0 | acct_depth_1 | acct_depth_2 | . . .
+----------------+----------------+----------------+
| 450000 | 450040 | | . . .
+----------------+----------------+----------------+
| 450000 | 450050 | 450051 | . . .
+----------------+----------------+----------------+
What would be the best way to achieve this? So far I have the following SQL:
select distinct
t1.acct,
t1.desc,
t2.acct,
t2.desc,
t3.acct,
t3.desc,
t4.acct,
t4.desc,
t5.acct,
t5.desc,
t6.acct,
t6.desc
from (select tree.primarykey acct, act.description desc
from hierarchy_table tree, acct_description_table act
where 1=1
and tree.depth = 0
and act.acct_value = tree.primarykey
) t1
left join (select tree.primarykey acct, tree.parentkey parent, act.description desc
from hierarchy_table tree, acct_description_table act
where 1=1
and tree.depth = 1
and act.acct_value = tree.primarykey
) t2
on 1=1 and t1.acct = t2.parent
...
...
...
left join (select tree.primarykey acct, tree.parentkey parent, act.description desc
from hierarchy_table tree, acct_description_table act
where 1=1
and tree.depth = 5
and act.acct_value = tree.primarykey
) t6
on 1=1 and t5.acct = t6.parent
As you can see with a query like this we'd be doing 5 different left join operations to complete the table. I've looked into recursion to help speed up this query. However, I'm not sure how to code it to work with our need of having columns corresponding to each depth.
Has anyone done something similar to this? Or does anyone know of a way that we could do this faster than the current query consisting of 5 different left joins? Thank you!
Oracle's CONNECT BY functionality is good for hierarchies and pretty efficient. Here's one way you might use it for your data:
with hierarchy_table ( primarykey, parentkey, depth ) as
-- This is test data, you would not have this WITH clause...
( SELECT '450000', null, 0 FROM DUAL UNION ALL
SELECT '450040', '450000', 1 FROM DUAL UNION ALL
SELECT '450050', '450000', 1 FROM DUAL UNION ALL
SELECT '450051', '450050', 2 FROM DUAL ),
acct_description_table ( acct_value, description ) AS
-- This is test data, you would not have this WITH clause...
( SELECT '450000', 'Acct #450000' FROM DUAL UNION ALL
SELECT '450040', 'Acct #450040' FROM DUAL UNION ALL
SELECT '450050', 'Acct #450050' FROM DUAL UNION ALL
SELECT '450051', 'Acct #450051' FROM DUAL )
-- Real query starts here
SELECT REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(ht.primarykey,':'),'[^:]+',1,1) acct1,
REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(ht.primarykey,':'),'[^:]+',1,2) acct2,
REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(ht.primarykey,':'),'[^:]+',1,3) acct3,
REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(ht.primarykey,':'),'[^:]+',1,4) acct4,
REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(ht.primarykey,':'),'[^:]+',1,5) acct5,
REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(ht.primarykey,':'),'[^:]+',1,6) acct6,
adt.description
FROM hierarchy_table ht
INNER JOIN acct_description_table adt ON adt.acct_value = ht.primarykey
WHERE CONNECT_BY_ISLEAF = 1
START WITH ht.parentkey IS NULL
CONNECT BY ht.parentkey = PRIOR ht.primarykey
AND ht.depth = PRIOR ht.depth + 1 -- There is actually no need for the "DEPTH" column in your table.
AND LEVEL <= 6;
+--------+--------+--------+-------+-------+-------+--------------+ | ACCT1 | ACCT2 | ACCT3 | ACCT4 | ACCT5 | ACCT6 | DESCRIPTION | +--------+--------+--------+-------+-------+-------+--------------+ | 450000 | 450040 | | | | | Acct #450040 | | 450000 | 450050 | 450051 | | | | Acct #450051 | +--------+--------+--------+-------+-------+-------+--------------+