Just wanted to pass on kudo's to a coworker who writes some outstanding
SQL. He had this multipage sql statement that does a full BOM explosion
in BPCS. He had to modify it to handle a special exception when someone
manually changes part numbers out of the system and does something like
have the parent included as one of the children also.
Not only am I impressed with his grasp of SQL (all learned solely from
the IBM reference manuals) but also by his comments.

From the coworker (looks so much prettier in WDSC with the colors and
all):

Here is the code for my BOM explosion using SQL. The 'cycle' command
prevents circular infinite loops. NOTE: This is not a full query.

...
, Exploded(Parent, Child, Qty
,LVL, TopParent) as
(select BPROD, BCHLD, BQREQ, 1, BPROD
from MBMClean
union all
select BPROD, BCHLD, BQREQ, LVL+1
, TopParent
from MBMClean
join Exploded
on BPROD=Child
// Configure BOM explosion to follow each path to its end.
// OrdCol controls this in a later "ORDER BY"
) search depth first by Child set OrdCol
// Prevent endless loops due to circular reference
cycle Child set cyclic_data to '1' default '0'

Rob Berendt

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].