22

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
Community
  • 1
  • 1
Will Lovett
  • 1,241
  • 3
  • 18
  • 35
  • 1
    I don't understand, how qty has to be calculated. Please explain. – Olaf H Jun 28 '13 at 07:22
  • 1
    Hi Olaf. This particular structure models a Bill of Material (BOM). It's a way of describing what components make up other components. The short of it is that the component qtys are multiplicative. For example, if there are 2 27s in a 25, then there would be 4 31s in a 25. Looking back, I did make a typo in my original post. There should only be 1 30 in a 25. – Will Lovett Jun 28 '13 at 16:14

2 Answers2

23

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 
Community
  • 1
  • 1
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
11

@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)

turbanoff
  • 2,439
  • 6
  • 42
  • 99
ThinkJet
  • 6,725
  • 24
  • 33
  • ThinkJet, this works great! After I ugpraded from 10g to 11g R2, all I had to do was copy and paste and it worked like a charm. The only thing I changed was putting multiply instead of addition, which gave me the correct qtys. I was wholly surprised by both you and Alex's answers. This helped me out quite a bit! – Will Lovett Jun 28 '13 at 16:17
  • On more question if you can ... When I try to turn your query into a view, I receive a SQL Parse Exception: Error(s) parsing SQL: syntax error near *!* in the following: with *!*recursion_view(base, parent_id, child_id, qty) as ( syntax error near *!* in the following: with recursion_view*!*(base, parent_id, child_id, qty) as ( – Will Lovett Jun 28 '13 at 16:25
  • Depending on how I try to create the view, I receive Jun 28, 2013 12:42:41 PM oracle.dbtools.db.DBUtil handleException SEVERE: Warning, unhandled exception: Closed Connection And another SEVERE: Warning, unhandled exception: ORA-00600: internal error code, arguments: [qkebCreateColInFro:1], [], [], [], [], [], [], [], [], [], [], [] – Will Lovett Jun 28 '13 at 16:46
  • 1
    @WillLovett - where are the `!` and `*` coming from in the error? You can turn it into a view by sticking `create view my_view as ` in front of it, and not changing anything else. If you're doing something else you might need to ask a new question showing the exact code you're using in the question. – Alex Poole Jun 28 '13 at 16:48
  • 1
    @AlexPoole I've posted two screenshots: http://www.williverstravels.com/JDev/Forums/StackOverflow/17358109/ViewError.jpg and http://www.williverstravels.com/JDev/Forums/StackOverflow/17358109/InternalError.jpg The first one with the ! and * is when I am using JDeveloper 11g, using the Database Navigator, right-click on View and select "New View". I receive the error when I click OK. I can indeed create the view through a sql script, but when I attempt to view the data, I receive the ORA_00600 error. – Will Lovett Jun 28 '13 at 17:26
  • @WillLovett - can't help you with JDeveloper. The ORA-600 looks like a bug, I can see two possibles. Works for me in 11.2.0.3 though. You said you upgraded to 11gR2; did you stop at 11.2.0.1 or patch up to the current patchset? Again, this is really a whole new question, and maybe one for [DBA.SE]. – Alex Poole Jun 28 '13 at 17:35
  • @AlexPoole Thanks again Alex. I downloaded straight from oracle website and installed. My current version is 11.2.0.2.0. Perhaps I need to patch somehow. Thank you again for your help. – Will Lovett Jun 28 '13 at 17:56
  • 1
    After spending a good 6 hours on this, I decide not to use JDev's graphical editor, and simply write SELECT * FROM BOMS_VIEW; And it works like a charm. I just can't use data tab to view the records in the editor. Wish I had known that 5 hours ago. – Will Lovett Jun 28 '13 at 21:52