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
where psthdr.pitnbr=itmrva.itnbr and
itmrva.csft<>'900 ' and itmrva.stid='11'
(select pstdtl.cinbr, qtypr where pstdtl.pinbr=psthdr.pinbr)
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 ...
WITH RPL (PART, SUBPART, QUANTITY) AS
( SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
FROM PARTLIST ROOT
WHERE ROOT.PART =
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
revision.ITRV = parent.PITR AND parent.pinbr='0050' and
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?
Last edited by frosterrj; 04-20-09 at 18:37.
Reason: upload bmp showing limited result set in second select has where clause
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.
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?