Menu
I've got a recursive query that's really stretching the limits of this Java monkey's SQL knowledge. Now that it's finally 1:30 in the AM, it's probably time to start looking for some help. This is one of the few times Google has failed me.
The table is as follows:
Parent_ID CHILD_ID QTY
25 26 1
25 27 2
26 28 1
26 29 1
26 30 2
27 31 1
27 32 1
27 33 2
I'm trying to get the following result, where the parent has every child listed below them. Note that the qty's cascade as well.
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 1
25 27 31 2
25 27 32 2
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
I've tried several deviations of the following to no avail.
SELECT *
FROM MD_BOMS
START WITH PARENT_ID is not null
CONNECT BY PRIOR CHILD_ID = PARENT_ID
ORDER BY PARENT_ID, CHILD_ID
I'm using the Oracle database. Any suggestions, ideas, etc. would be greatly appreciated. This seems close, but I'm not sure if it's what I'm looking for: Retrieve all Children and their Children, recursive SQL
Based on ( Retrieve all Children and their Children, recursive SQL )I've also tried the following but receive an "illegal reference ofa query name in WITH clause" error:
with cte as (
select CHILD_ID, PARENT_ID, CHILD_ID as head
from MD_BOMS
where PARENT_ID is not null
union all
select ch.CHILD_ID, ch.PARENT_ID, p.head
from MD_BOMS ch
join cte pa
on pa.CHILD_ID = ch.PARENT_ID
)
select *
from cte
798
To find out who that child's parent is, you have to look at the column parent_id , find the same ID number in the id column, and look in that row for the parent's name. In other words, Jim Cliffy has no parents in this table; the value in his parent_id column is NULL .
Thus, a recursive CTE consists of a nonrecursive SELECT part followed by a recursive SELECT part. Each SELECT part can itself be a union of multiple SELECT statements. The types of the CTE result columns are inferred from the column types of the nonrecursive SELECT part only, and the columns are all nullable.
When you issue a data definition language (DDL) statement, Oracle Database implicitly issues recursive SQL statements that modify data dictionary information. Users need not be concerned with the recursive SQL internally performed by Oracle Database. Previous.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
