title: How to retrieve all recursive children of parent row in Oracle SQL?
body: <p>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.</p>
  
  <p>The table is as follows:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I've tried several deviations of the following to no avail.</p>
  
  <pre><code>SELECT *
  FROM MD_BOMS
  START WITH PARENT_ID is not null
  CONNECT BY PRIOR CHILD_ID = PARENT_ID
  ORDER BY PARENT_ID, CHILD_ID
  </code></pre>
  
  <p>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: <a href="https://stackoverflow.com/questions/3299496/retreive-all-children-and-their-children-recursive-sql">Retrieve all Children and their Children, recursive SQL</a></p>
  
  <p>Based on ( <a href="https://stackoverflow.com/questions/3299496/retreive-all-children-and-their-children-recursive-sql?lq=1">Retrieve all Children and their Children, recursive SQL</a> )I've also tried the following but receive an "illegal reference ofa  query name in WITH clause" error:</p>
  
  <pre><code>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
  </code></pre>