Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 27 Jul 2011 @ 13:52:21 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


Subj:   Indented Bill of Material Query?
 
From:   Nathan_S_Smith

Hi,

Has anyone developed an indented BOM query on Teradata using the WITH RECURSIVE query?

I have created a recursive query using a depth column which shows how deep in the relationship an item is; however, I cannot determine how to retain the sequential relationship between the top level items to the component items. I am assuming since the results are kept in spool, there is no guarantee that dumping the results at the end using a simple select will report the results in the order created. If I sort the result by the depth level, then the indented relationship between the assemblies to components is lost.

This is the simple query I am using taken from the SQL manual:

     with recursive bom_table (item_nbr, cmpnt_item_nbr, depth) as ( select  micb.itm_nbr,
     micb.cmpnt_itm_nbr, 0 as depth
       from MFG_BOM micb
       where micb.itm_nbr = '434232'
       union all
        select  cmpbom.itm_nbr, cmpbom.cmpnt_itm_nbr, depth+1 as newdepth
        from bom_table bom
        join MFG_BOM cmpbom
        on bom.cmpnt_item_nbr = cmpbom.itm_nbr
                     where newdepth <= 25
     )
     select * from bom_table order by depth;

Based on the above, I get:

     Item Nbr  Cmpnt_Item_Nbr Depth
     434232                  41232                    0
     41232                    56732                    1
     41232                    78543                    1
     56732                    85421                    2
     56732                    45876                    2

What I would like to see is the following:

     Item Nbr  Cmpnt_Item_Nbr Depth
     434232                  41232                    0
     41232                    56732                    1
     56732                    85421                    2
     56732                    45876                    2
     41232                    78543                    1

The above is a simple example, but with many depth 0 items and many different levels of components, tracing through the list in the first example could be difficult.

Any suggestions would be appreciated.


Thanks,

Nathan



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016