Re: BOM Explosion

[CRPence <CRPbottle@xxxxxxxxx>]

  I think the SEARCH DEPTH FIRST clause is required for the 
recursive CTE to properly expand the item are nested properly, and 
generate\establish a sorting column.

http://www.google.com/#q=recursive+query+bom+explode+depth+first
www.ibm.com/systems/resources/systems_i_software_db2_pdf_rcte_olap.pdf

  Of possible note, the recursive CTE is referred to as RCTE in the 
above article; i.e. possible value as a search token

Regards, Chuck

Tom E Stieger wrote:
> Here is an SQL that works to start with.
>
> WITH temp_pstruc (RELVL, ITEM, QTY) AS           
> (                                                
> SELECT 0, PINBR, 0                               
> FROM AMFLIBP/PSTRUC  where pinbr like '%5022322%'
> UNION ALL SELECT a.relvl + 1, b.cinbr, b.qtypr   
> FROM temp_pstruc AS a join amflibp/pstruc AS b   
> on ITEM = b.pinbr                                
> )                                                
>                                                  
> SELECT DISTINCT *                                
> FROM temp_pstruc                                 
>
> Here is the resulting data, which is valid based on my source
>
>  RELVL   ITEM                            QTY
>      0   5022322                        .000
>      1   7022322                       1.000
>      1   7022322.1                     1.000
>      1   10566.16                      2.000
>      2   7022322.2                     1.000
>      2   9022322                       1.000
>      2   7022322.3                     1.000
>      2   9022322.1                     1.000
>
> It is not quite in the order that I want though. Does anyone know how to get it to order like this:
>
>  RELVL   ITEM                            QTY
>      0   5022322                        .000
>      1   7022322                       1.000
>      2   7022322.2                     1.000
>      2   9022322                       1.000
>      1   7022322.1                     1.000
>      2   7022322.3                     1.000
>      2   9022322.1                     1.000
>      1   10566.16                      2.000
>
>
> ***DISCLAIMER - Use this SQL at your own risk.  I am not responsible if your recursion loops indefinitely.***
>
> CRPence wrote:
>
>    My browser is unavailable, but what about searching the web for examples of recursive SQL queries to do that?  I seem to recall running across one or two when trying to learn about recursive queries.  SQL is available on all IBM i & i5/OS; later releases support recursive queries [with restriction to the SQL Query Engine; SQE, which means query of the physical file(s) only].  HTH.
>
>
> Bob Voltz wrote:
> > <<SNIP>>
> >
> > I am looking for a program that will do a BOM Explosion and produce 
> > results that match MAPICS Inquiry AMEC72.
> >
> > It needs to output to a file.
> >
> > Someone did send me a program that will blow through the BOM, but the 
> > quantities don't match the MAPICS Inquiry AMEC72.