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
A BOM is recursive if the product contains a component with the same object number as the superior assembly. This is often due to input errors, but in individual cases it may be intentional. A component has the same material number as a higher-level product.
A recursive query is one that is defined by a Union All with an initialization fullselect that seeds the recursion. The iterative fullselect contains a direct reference to itself in the FROM clause.
Companies use a BOM (Bill of Materials) to itemize the components and sub assemblies used to construct their products.
Recursion is implemented in standard SQL-99 using common table expressions (CTEs). DB2, Microsoft SQL Server, Oracle and PostgreSQL all support recursive queries using CTEs.
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 '')
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