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
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
select BPROD, BCHLD, BQREQ, LVL+1
// 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'