I am new to the Oracle Hierarchical Queries. I have one table and have the below data.
Table Data Result Data My question is why parent id(100) is not included in the result? Below is the query.
select id, lpad(' ',4*(LEVEL - 1)) || CHILD CHILD, LEVEL from temp START WITH PARENT = 100 CONNECT BY PARENT = PRIOR CHILD;
Regards, Bhushan
Advertisement
Answer
If you to include starting value in your output then use union all:
select 0 id, '100' child, 0 lvl from dual union all select id, lpad(' ', 4 * level ) || child, level from temp start with parent = 100 connect by parent = prior child
or recursive CTE:
with c(id, child, lvl) as ( select 0, '100', 0 from dual union all select t.id, lpad(t.child, (c.lvl + 2) * 4, ' '), c.lvl + 1 from c join temp t on t.parent = c.child) search depth first by id set seq select id, child, lvl from c;
or add it to source data at first:
select id, lpad(' ', 4 * (level-1) ) || child child, level from (select id, parent, child from temp union all select null, null, 100 from dual ) start with child = 100 connect by parent = prior child