Archives of the TeradataForum
Message Posted: Wed, 27 Jul 2011 @ 13:52:21 GMT
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|