title: Recursive Query for Bill Of Materials
body: <p>I've just started teaching myself SQL recently and have been able to piece together almost everything I need from various tutorials, but this one has me banging my head against the wall.  We have a table that contains all the bill of material information for our products.  I only need 4 of the columns from it - PPN_I, CPN_I, QUANTITY_I, BOMNAME_I - which are Item Number, Raw Material number, quantity, and the BOMName, respectively.  Many of the BOMs contain subassemblies.  I need a result set that lists all the components of the BOM, regardless of level.  I'm pretty sure I need to use a recursive query but can't quite get it and any help would be appreciated.  I know there are several other BOM questions out there, but all of them seem to have different table structures. So -</p>
  
  <p>To start, I was just trying to get the results for one specific item to facilitate my guess and check work.  When I'm done I'll need the report for all items, or at least a list of items.  Many of these items are configurable and have multiple BOMs.  To get the default set up I am looking for a blank BOM name.</p>
  
  <p>I can run this and get the first level:</p>
  
  <pre><code>    select bm.ppn_i, bm.cpn_i, bm.bomname_i, bm.QUANTITY_I, 1 as BOMLevel
      from BM010115 bm
      where bm.PPN_I like '0123105-HWT' and bm.BOMNAME_I like ''
  </code></pre>
  
  <p>The problem is that one (or more) of the rows will have cpn_i value that is a subassembly.  To see what makes up the subassemply I need to put each of the results from the first query back into the same query.</p>
  
  <pre><code>    select bm.ppn_i, bm.cpn_i, bm.bomname_i, bm.QUANTITY_I, 2 as BOMLevel
      from BM010115 bm
      where bm.PPN_I like 'ZC-BASESUBLIM' and bm.BOMNAME_I like ''
  </code></pre>
  
  <p>Obviously this is not the most effective way of doing this.  I've tried and I've tried, but I just can't seem to get the results right.  I've gone through lots of different versions of this and here's what I've done most recently/gotten closest.</p>
  
  <pre><code>    With BMStudy as
      (select bm.ppn_i, bm.cpn_i, bm.bomname_i, bm.QUANTITY_I, 1 as BOMLevel
      from BM010115 bm
      where bm.PPN_I like '0123105-HWT' and bm.BOMNAME_I like ''
  
      UNION ALL
  
      select bb.ppn_i, ba.cpn_i, bb.bomname_i, ba.quantity_i, 2 as BOMLevel
      from BM010115 bb, BMStudy ba
      where bb.BOMNAME_I like '' and ba.PPN_I = bb.CPN_I)
      select * from BMStudy
  </code></pre>
  
  <p>This only returns the first level of results.  I don't believe any of the BOMs are more than 3 levels deep, but I'd like to have a column that indicates which level it is.  Can someone point me in the right direction or give me some pointers as to where I went wrong?</p>
  
  <p>TL;DR - Need query that pulls all components in a BOM, and then pulls the BOM components for all the results of the first query and adds it to the result set.</p>
  
  <p><br>Thanks</p>