Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Recursive query for a Bill of Materials table


Recursive query for a Bill of Materials table

Author
Message
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45248 Visits: 39931
david.moule (4/11/2011)
Hi Jeff,

I got your .net email and have sent a test reply to it.....



I didn't get it as of 5:26 PM

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dave hants
dave hants
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 539
I take it you didn't actually read what I sent you, then.

--Jeff Moden
--------------------------------------------------------------------------------


Yep of course I read it :-)

But I haven't had a chance to convert my code to that method yet. Still work in progress. The seeding list of the top level BOM's seems to be the key. I've posted my test sample so that it is clear what I am aiming for, and where I am upto so far..... your signature block pointer was very helpful...
Greg Snidow
Greg Snidow
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1611 Visits: 2478
Jeff/Dave - I've actually used the BOL example as a template for several procedures since this post began, and I am very interested in learning of the solution to Dave's problem. Any way you guys could post the solution to the problem?

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45248 Visits: 39931
Greg Snidow (4/22/2011)
Jeff/Dave - I've actually used the BOL example as a template for several procedures since this post began, and I am very interested in learning of the solution to Dave's problem. Any way you guys could post the solution to the problem?


Sure... as usual, comments in the code...
--===== Conditionally drop the test table to make reruns in SSMS easier.
-- (Note that this is NOT a part of the solution.)
IF OBJECT_ID('TempDB..#dmBOM','U') IS NOT NULL DROP TABLE #dmBOM;

--===== Create and populate the test table on the fly
-- (Note that this is NOT a part of the solution.)
SELECT Parent_Item = CAST(d.Parent AS VARCHAR(20)),
Child_Item = CAST(d.Child AS VARCHAR(20))
INTO #dmBom
FROM (
SELECT '1', '2' UNION ALL
SELECT '1', '3' UNION ALL
SELECT '1', '4' UNION ALL
SELECT '1', '5' UNION ALL
SELECT '1', '6' UNION ALL
SELECT '1', '7' UNION ALL
SELECT '2', 'a' UNION ALL
SELECT '2', 'b' UNION ALL
SELECT '2', 'c' UNION ALL
SELECT '3', '12' UNION ALL
SELECT '3', '13' UNION ALL
SELECT '3', '14' UNION ALL
SELECT '4', 'x' UNION ALL
SELECT '4', 'y' UNION ALL
SELECT '4', 'z' UNION ALL
SELECT '9', 'c' UNION ALL
SELECT '9', 'd' UNION ALL
SELECT '9', 'e'
) d (Parent, Child)
;
--=================================================================================================
-- Solve the problem. Produce the list of parts in the expected "drill down order".
-- This lists more columns than what the OP requires but they have been included for clarity.
--=================================================================================================
--===== Conditionally drop the working table to make reruns in SSMS easier.
IF OBJECT_ID('TempDB..#WorkingBom','U') IS NOT NULL DROP TABLE #WorkingBom;

--===== Expand the hierarchy in "drill down order".
WITH
ctePartExplosion AS
( --=== This "anchor" section finds only the top level parts and put's them into level 1
SELECT Child_Item = p.Parent_Item, Parent_Item = CAST(NULL AS VARCHAR(20)), AssemblyLevel = 1,
HierarchicalPath = CAST('\'+p.Parent_Item AS VARCHAR(4000))
FROM ( ----- Find all parent's who are not also children. This is the top level.
SELECT Parent_Item FROM #dmBom
EXCEPT
SELECT Child_Item FROM #dmBom
) p
UNION ALL
--===== This "recursive" part of our query steps through all levels until there are no more
SELECT rcsv.Child_Item, rcsv.Parent_Item, AssemblyLevel = cte.AssemblyLevel + 1,
HierarchicalPath = CAST(cte.HierarchicalPath + '\'+rcsv.Child_Item AS VARCHAR(4000))
FROM #dmBom rcsv
INNER JOIN ctePartExplosion cte ON rcsv.Parent_Item = cte.Child_Item
) --=== This simply displays things in the expected "drill down order".
SELECT Child_Item, Parent_Item, AssemblyLevel, HierarchicalPath
FROM ctePartExplosion
ORDER BY HierarchicalPath
;




--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45248 Visits: 39931
For more of a visual impact....
--===== Conditionally drop the test table to make reruns in SSMS easier.
-- (Note that this is NOT a part of the solution.)
IF OBJECT_ID('TempDB..#dmBOM','U') IS NOT NULL DROP TABLE #dmBOM;

--===== Create and populate the test table on the fly
-- (Note that this is NOT a part of the solution.)
SELECT Parent_Item = CAST(d.Parent AS VARCHAR(20)),
Child_Item = CAST(d.Child AS VARCHAR(20))
INTO #dmBom
FROM (
SELECT '1', '2' UNION ALL
SELECT '1', '3' UNION ALL
SELECT '1', '4' UNION ALL
SELECT '1', '5' UNION ALL
SELECT '1', '6' UNION ALL
SELECT '1', '7' UNION ALL
SELECT '2', 'a' UNION ALL
SELECT '2', 'b' UNION ALL
SELECT '2', 'c' UNION ALL
SELECT '3', '12' UNION ALL
SELECT '3', '13' UNION ALL
SELECT '3', '14' UNION ALL
SELECT '4', 'x' UNION ALL
SELECT '4', 'y' UNION ALL
SELECT '4', 'z' UNION ALL
SELECT '9', 'c' UNION ALL
SELECT '9', 'd' UNION ALL
SELECT '9', 'e'
) d (Parent, Child)
;
--=================================================================================================
-- Solve the problem. Produce the list of parts in the expected "drill down order".
-- This lists more columns than what the OP requires but they have been included for clarity.
--=================================================================================================
--===== Conditionally drop the working table to make reruns in SSMS easier.
IF OBJECT_ID('TempDB..#WorkingBom','U') IS NOT NULL DROP TABLE #WorkingBom;

--===== Expand the hierarchy in "drill down order".
WITH
ctePartExplosion AS
( --=== This "anchor" section finds only the top level parts and put's them into level 1
SELECT Child_Item = p.Parent_Item, Parent_Item = CAST(NULL AS VARCHAR(20)), AssemblyLevel = 1,
HierarchicalPath = CAST('\'+p.Parent_Item AS VARCHAR(4000))
FROM ( ----- Find all parent's who are not also children. This is the top level.
SELECT Parent_Item FROM #dmBom
EXCEPT
SELECT Child_Item FROM #dmBom
) p
UNION ALL
--===== This "recursive" part of our query steps through all levels until there are no more
SELECT rcsv.Child_Item, rcsv.Parent_Item, AssemblyLevel = cte.AssemblyLevel + 1,
HierarchicalPath = CAST(cte.HierarchicalPath + '\'+rcsv.Child_Item AS VARCHAR(4000))
FROM #dmBom rcsv
INNER JOIN ctePartExplosion cte ON rcsv.Parent_Item = cte.Child_Item
) --=== This simply displays things in the expected "drill down order".
SELECT Child_Item = SPACE((AssemblyLevel-1)*2)+Child_Item, Parent_Item, AssemblyLevel, HierarchicalPath
FROM ctePartExplosion
ORDER BY HierarchicalPath
;




--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dave hants
dave hants
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 539
Here's my code for the finished example where a list of Top Bills of Materials is used and a product structure table, to create a BOM explosion:

------------------------------------------------------------------------
-- BOM explosion example, using a seed list
-- Thanks to jhood for initial example, modified to explode a list of top bills of material for dm:

-- Drop temp tables if they exist
IF OBJECT_ID('TempDB..#dmBOM','U') IS NOT NULL
DROP TABLE #dmBOM
IF OBJECT_ID('TempDB..#dmBOM_expanded','U') IS NOT NULL
DROP TABLE #dmBOM_expanded
IF OBJECT_ID('TempDB..#dmBOM_final_output','U') IS NOT NULL
DROP TABLE #dmBOM_final_output

-- Input table is the product structure table
-- Desired output is a table containing all unique parent and child parts

-- Create the product structure table,
-- each top bill of material with all of their child components
CREATE TABLE #dmBOM (parent_item varchar(20), child_item varchar(20))

INSERT INTO #dmBOM VALUES ('1', '2')
INSERT INTO #dmBOM VALUES ('1', '3')
INSERT INTO #dmBOM VALUES ('1', '4')
INSERT INTO #dmBOM VALUES ('1', '5')
INSERT INTO #dmBOM VALUES ('1', '6')
INSERT INTO #dmBOM VALUES ('1', '7')
INSERT INTO #dmBOM VALUES ('2', 'a')
INSERT INTO #dmBOM VALUES ('2', 'b')
INSERT INTO #dmBOM VALUES ('2', 'c')
INSERT INTO #dmBOM VALUES ('3', '12')
INSERT INTO #dmBOM VALUES ('3', '13')
INSERT INTO #dmBOM VALUES ('3', '14')
INSERT INTO #dmBOM VALUES ('4', 'x')
INSERT INTO #dmBOM VALUES ('4', 'y')
INSERT INTO #dmBOM VALUES ('4', 'z')
INSERT INTO #dmBOM VALUES ('9', 'd')
INSERT INTO #dmBOM VALUES ('9', 'e')
INSERT INTO #dmBOM VALUES ('9', 'f')
INSERT INTO #dmBOM VALUES ('a', 'g')
INSERT INTO #dmBOM VALUES ('b', 'h')
INSERT INTO #dmBOM VALUES ('c', 'i')

-- Create the intermediate output table and initialise it with all the
-- top bills of material that need exploding
CREATE TABLE #dmBOM_expanded
(item varchar(20), i_level INT)

INSERT INTO #dmBOM_expanded (item, i_level)
SELECT distinct parent_item, 0 AS i_level
FROM #dmBOM

-- Create and set a level counter, then explode the hierarchy using the product
-- structure table, then remove duplicates to get the final output:
DECLARE @CurrentLevel INT
SET @CurrentLevel = 0
WHILE @@ROWCOUNT > 0
BEGIN
SET @CurrentLevel = @CurrentLevel + 1
INSERT INTO #dmBOM_expanded (item, i_level)
SELECT p.child_item as item, @CurrentLevel AS i_level
FROM #dmBOM as p
INNER JOIN #dmBOM_expanded as h
ON p.parent_item = h.item
AND h.i_level = @CurrentLevel - 1
END
select distinct item
into #dmBOM_final_output
from #dmBOM_expanded
order by item

select * from #dmBOM order by parent_item, child_item
select * from #dmBOM_expanded order by item, i_level
select * from #dmBOM_final_output order by item

-----------------------------------------------------------------------------------
-- Cleanup temporary tables when verified
IF OBJECT_ID('TempDB..#dmBOM','U') IS NOT NULL
DROP TABLE #dmBOM
IF OBJECT_ID('TempDB..#dmBOM_expanded','U') IS NOT NULL
DROP TABLE #dmBOM_expanded
IF OBJECT_ID('TempDB..#dmBOM_final_output','U') IS NOT NULL
DROP TABLE #dmBOM_final_output
-----------------------------------------------------------------------------------

Regards, Dave
lenar.chavez
lenar.chavez
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 7
I have a working BOM recursive CTE, much like this one, but now I need to be able to input the part number I need the BOM from and have no clue how to do it.

If I put in a where clause right after the first part of the CTE with a specific part number it will give me the results I need but I need to be able to tell the database which part number I need the BOM from easily.

Any ideas on how to achieve this?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search