How to retrieve all recursive children of parent row in Oracle SQL?
You are close:
select connect_by_root parent_id base, parent_id, child_id, qty
from md_boms
connect by prior child_id = parent_id
order by base, parent_id, child_id;
BASE PARENT_ID CHILD_ID QTY
---------- ---------- ---------- ----------
25 25 26 1
25 25 27 2
25 26 28 1
25 26 29 1
25 26 30 2
25 27 31 1
25 27 32 1
25 27 33 2
26 26 28 1
26 26 29 1
26 26 30 2
27 27 31 1
27 27 32 1
27 27 33 2
14 rows selected
The connect_by_root
operator gives you the base parent_id
.
SQL Fiddle.
I'm not sure how you're calculating your qty
. I'm guessing you want the total for the path to the child, but that doesn't match what you've shown. As a starting point, then, borrowing very heavily from this answer, you could try something like:
with hierarchy as (
select connect_by_root parent_id base, parent_id, child_id, qty,
sys_connect_by_path(child_id, '/') as path
from md_boms
connect by prior child_id = parent_id
)
select h.base, h.parent_id, h.child_id, sum(e.qty)
from hierarchy h
join hierarchy e on h.path like e.path ||'%'
group by h.base, h.parent_id, h.child_id
order by h.base, h.parent_id, h.child_id;
BASE PARENT_ID CHILD_ID SUM(E.QTY)
---------- ---------- ---------- ----------
25 25 26 1
25 25 27 2
25 26 28 2
25 26 29 2
25 26 30 3
25 27 31 3
25 27 32 3
25 27 33 4
26 26 28 1
26 26 29 1
26 26 30 2
27 27 31 1
27 27 32 1
27 27 33 2
14 rows selected
@AlexPoole answer is great, I just want to extend his answer with more intuitive variant of query for summing values along a path.
This variant based on recursive subquery factoring feature, introduced in Oracle 11g R2
.
with recursion_view(base, parent_id, child_id, qty) as (
-- first step, get rows to start with
select
parent_id base,
parent_id,
child_id,
qty
from
md_boms
union all
-- subsequent steps
select
-- retain base value from previous level
previous_level.base,
-- get information from current level
current_level.parent_id,
current_level.child_id,
-- accumulate sum
(previous_level.qty + current_level.qty) as qty
from
recursion_view previous_level,
md_boms current_level
where
current_level.parent_id = previous_level.child_id
)
select
base, parent_id, child_id, qty
from
recursion_view
order by
base, parent_id, child_id
SQLFiddle example (extended with one data row to demonstrate work with more then 2 levels)