For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
select child from test_hierarchy minus select parent p from test_hierarchy;
with rec(rootParent,parent,child,Lv) as( select parent,parent,child,1 from test_hierarchy where parent = 'a' union all select a.rootParent,b.parent,b.child,a.Lv+1 from rec a Join test_hierarchy b on a.child = b.parent where (b.parent,b.child) not in(select c.parent,c.child from test_hierarchy c start with c.parent = 'a' connect by prior c.child = c.parent and Level <= a.Lv)) select rootParent,parent,child,LV from rec a where not exists(select 1 from rec b where a.child = b.parent); R P C LV - - - -- a d e 2 a d f 2
SQL> WITH test_hierarchy as( 2 SELECT 'a' parent, 'b' child FROM dual UNION ALL 3 SELECT 'a','c' FROM dual UNION ALL 4 SELECT 'a','d' FROM dual UNION ALL 5 SELECT 'b','c' FROM dual UNION ALL 6 SELECT 'b','d' FROM dual UNION ALL 7 SELECT 'c','d' FROM dual UNION ALL 8 SELECT 'd','e' FROM dual UNION ALL 9 SELECT 'd','f' FROM dual) 10 Select LPAD(' ', level*2, ' ')||parent||'-'||child hier, 11 connect_by_root(parent) root, level lv 12 from test_hierarchy 13 -- where connect_by_isleaf = 1 14 start with parent = 'a' 15 connect by prior child = parent; HIER R LV --------------- - ---------- a-b a 1 b-c a 2 c-d a 3 d-e a 4 d-f a 4 b-d a 2 d-e a 3 d-f a 3 a-c a 1 c-d a 2 d-e a 3 d-f a 3 a-d a 1 d-e a 2 d-f a 2
SELECT get_leaves ('A') FROM my_table;
GET_LEAVES -------------------- E,F