Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2009
    Posts
    5

    Unanswered: Recursive Join for Bill of Materials?

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

  2. #2
    Join Date
    Feb 2009
    Posts
    114
    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!

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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:

  4. #4
    Join Date
    Apr 2009
    Posts
    5

    Tonkuma, did you find that reference online or physical book?

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

    Thanks,
    Robert

  5. #5
    Join Date
    Apr 2009
    Posts
    5

  6. #6
    Join Date
    Apr 2009
    Posts
    5

    Recursion not following the where clause...

    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
    Attached Thumbnails Attached Thumbnails ScreenHunter_1.bmp   ScreenHunter_2.bmp  
    Last edited by frosterrj; 04-20-09 at 18:37. Reason: upload bmp showing limited result set in second select has where clause

  7. #7
    Join Date
    Feb 2009
    Posts
    114
    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.

  8. #8
    Join Date
    Apr 2009
    Posts
    5
    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?

  9. #9
    Join Date
    Feb 2009
    Posts
    114
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •