I'm trying to get an exploded bill of materials from my MAPICS Header and Detail files. Saw a post on this from 2003, and didn't seem to make sense.

Tables: PSTHDR(parent) PSTDTL(child)
Fields: PITNBR,SBQTY PINBR,CINBR, QYTPR

will also need to create a where clause to an item master to get proper engineering status, site, etc.

This seems like it should be pretty straight forward. Just want a single list for a parent item, std quantity, and it's children and quantity, some of the children can be parents with children themselves. If an item has children its in the PSTHDR and PSTDTL, if it has no children, only exists in PSTDTL.

The database is DB2/400 (V5R4 or 5), but trying to write SQL that works via MSSQL.exe via ODBC. Reference to the forum post above used as with syntax so that's why it probably doesn't with with MS SQL. If MSSQL will not work to view the sql, I can use strsql on the 400 or Ops Navigator.

Does this require a temp table?

My start at the code looks like this:
(select psthdr.pinbr, psthdr.sbqty
from
S108A4BC.AMFLIB.psthdr,108A4BC.AMFLIB.itmrva
where psthdr.pitnbr=itmrva.itnbr and
itmrva.csft<>'900 ' and itmrva.stid='11'
and itmrva.itnbr='0050')
union all
(select pstdtl.cinbr, qtypr where pstdtl.pinbr=psthdr.pinbr)


TIA for any help..

I thought it "should be pretty straight forward" .....
Of course you need an inline temp table, how otherwise you make it recurse!!?
And the second have of the union must reference the temp table - itself ...
Have fun!

You can see some examples of Bill of Materials
on "IBM Systems - iSeries DB2 for i5/OS SQL Reference Version 5 Release 4".

Chapter 4. Queries

select-statement

common-table-expression

Recursion example: bill of materials

Example 1: Single level explosion:

Example 2: Summarized explosion:

Example 3: Controlling depth:

Link? Almost impossible to find anything on IBM site...

Thanks,
Robert

http://publib.boulder.ibm.com/infoce...2/rbafzmst.pdf

From the Iseries manual I got this as a template:

WITH RPL (PART, SUBPART, QUANTITY) AS
( SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
FROM PARTLIST ROOT
WHERE ROOT.PART =
UNION ALL
SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
FROM RPL PARENT, PARTLIST CHILD
WHERE PARENT.SUBPART = CHILD.PART ) (parent's child=child's parent?)
SELECT DISTINCT PART, SUBPART, QUANTITY

which let me to this actual code:

WITH RPL ( pinbr, cinbr, qtypr) AS
( SELECT parent.pinbr, parent.cinbr, parent.QTYpr
FROM s108a4bc.amflib.pstdtl parent, s108a4bc.amflib.ITMRVA revision
where
parent.pinbr=revision.itnbr and
revision.ITRV = parent.PITR AND
parent.pinbr='0050' and
revision.CFST='300' AND
revision.STID='11'
UNION ALL
SELECT child.pinbr,child.cinbr, child.qtypr
FROM s108a4bc.amflib.pstdtl child, s108a4bc.amflib.pstdtl parent
WHERE parent.cinbr = child.Pinbr)
SELECT DISTINCT Pinbr,cinbr, qtypr FROM RPL

This returns all records, even though top select is saying only look at '0050'. IF I put that in the second select, I only get 1 recursion 0050 and it's children, not the children's children as well - as expected.

Any ideas or see any obvious mistakes?

Thanks...
Robert

As you traverse the BOM tree, you should keep track of all visited nodes. This can be done by assigning a unique ID to each of the nodes. I typically use the concatenation of unique IDs of all the nodes between root and the current node (including current, of course) - this is commonly called path (from root to current node). Your query must ensure that a node is never traversed twice. Also, record tree level number (distance from root) along with node ID and limit traversing to some number that is higher than the depth of the tree can ever get - this is useful to ensure that there are no infinite loops in your logic and that DB2 does not put out a warning message about possibility of infinite loops.

thanks,
but need to figure out what part controls the whole tree recurse. Currenlty cant get past one sub-level without getting everything in the table. The where clause in the first select does not seem to contol ALL the children's start point (pic 2 in my previous post), and where clause in child makes it stop at one level (pic 1)....

Can you tell why I can't get one item's exploded BOM from my code?

Because there is no recursion here! I do not see RPL mentioned in its own definition. I see it in the SQL you quoted from the manual, but not in your rewrite.