sql - Recursive Query for Bill Of Materials

Question

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 -

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.

I can run this and get the first level:

    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 ''

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.

    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 ''

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.

    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

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?

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.

Thanks

Answer 1

I think your logic was basically ok. Here are two improvements. First, the level is incremental so you can see what happens with the recursion. Second, it uses an explicit join:

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, (BOMLevel + 1) as BOMLevel
      from BMStudy ba join
           BM010115 bb
           on ba.cpn_i = bb.ppn_i
      where bb.BOMNAME_I like ''
     )
select * from BMStudy;

I suspect your problem is the condition where bb.BOMNAME_I like ''. Is it possible that this value is really NULL rather than blank?

You should also check what this non-recursive, one-level down query does:

      select bb.ppn_i, ba.cpn_i, bb.bomname_i, ba.quantity_i, (BOMLevel + 1) as BOMLevel
      from BM010115 ba join
           BM010115 bb
           on ba.cpn_i = bb.ppn_i
      where bb.BOMNAME_I like '' and
            (ba.PPN_I like '0123105-HWT' and ba.BOMNAME_I like '')