Archives of the TeradataForum

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

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

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