April 1, 2011 at 7:22 am
david.moule (4/1/2011)
Many thanks for the reply Jeff. I'll work on a test routine as per your first link in your signature block.With work pressure it could take me a few days to put it together.
(I'm intrigued you say not to use the BOL example, because it very nearly works for me.... and equally intrigued about what you have up your sleeve 🙂
Thanks!
Dave
I used to (obviously, about 4 years back) use the BOL example because, like a lot of folks, you don't know what you don't know. 😛
Looking back through this thread, you don't need to provide any test data. The example that JHood wrote above (which includes a modicum of test data) on this very same thread is the very same method I was going to suggest that you replace the BOL example with. It's a much more set-based method (Celko calls it "Lasagne" code because of its "layering" effect) and is a direct replacement for the CTE method. After all, all the recursive CTE does is load the root nodes and then load each level in a loop. The loops are simply a control mechanism to control which set of nodes are loaded for each level.
JHood also creates a "hierarchy path" in his code which he uses for sorting the data in "hierarchical order". As he says in his post, it's 9 times more efficient than the BOL example.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2011 at 12:09 pm
David, you have mail. Please check your "SPAM Locker" as it seems than my ISP has been labeled as a source of SPAM.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 5, 2011 at 4:43 pm
Hi Jeff,
I can't see any spam in my junk mail... or anything in my PM box....
April 5, 2011 at 5:02 pm
david.moule (4/5/2011)
Hi Jeff,I can't see any spam in my junk mail... or anything in my PM box....
Is the email address you provided for this site a good one? Keep in mind that I can't actually see it and if I could, I wouldn't post it here. If you want, send me an email via this site (I know it works) and I'll respond. Post that you've sent it here, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 7, 2011 at 11:19 am
Hi Jeff,
Many thanks for your email, which did arrive. I replied but got an undeliverable notice. Can you resend to my email address again, maybe with an alternate reply-to id?
April 7, 2011 at 2:37 pm
I sent you another email via this site with my email address in plain text. Notice that it's a .Net and not a .Com address.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 11, 2011 at 2:32 pm
Hi Jeff,
I got your .net email and have sent a test reply to it.....
April 11, 2011 at 2:39 pm
Here's my sample code for the bill of material explosion problem. Using the BOL article on "Expanding Hierarchies"... I took the example code and modified it as below. My problem is that it needs the top level BOM in order to start the expansion, whereas I want the output from the expansion of all the top level BOM's in my list of BOM's....
================================================================
Product structure is parent part and child part.
Held in bom table
I need to expand this table to show parent part, and each child part, (including treating each child part as
a parent part and expanding that hierarchy too)
bom table:
parent part child part
1 2
1 3
1 4
1 5
1 6
1 7
2 a
2 b
2 c
3 12
3 13
3 14
4 x
4 y
4 z
9 c
9 d
9 e
desired output, bom_expanded table:
1
2
3
4
5
6
7
a
b
c
12
13
14
x
y
z
9
c
d
e
actual output using the sample code below is missing the last 4 parts:
bom_expanded table:
1
2
3
4
5
6
7
a
b
c
12
13
14
x
y
z
----------------------
--===== Drop test 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..#dmSTACK','U') IS NOT NULL
DROP TABLE #dmSTACK
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', 'c')
INSERT INTO #dmBOM VALUES ('9', 'd')
INSERT INTO #dmBOM VALUES ('9', 'e')
CREATE TABLE #dmBOM_expanded (item varchar(20))
CREATE TABLE #dmSTACK (item varchar(20), level int)
DECLARE @current varchar(20)
DECLARE @level int
DECLARE @line varchar(20)
SET @current = '1'
INSERT INTO #dmSTACK VALUES (@current, 1)
SET @level = 1
WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #dmSTACK WHERE level = @level)
BEGIN
SELECT @current = item
FROM #dmSTACK
WHERE level = @level
SELECT @line = @current
INSERT INTO #dmBOM_expanded select @line
DELETE FROM #dmSTACK WHERE level = @level AND item = @current
INSERT #dmSTACK
SELECT [child_item], @level + 1
FROM #dmBOM
WHERE [parent_item] = @current
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END -- BEGIN
ELSE
SELECT @level = @level - 1
END -- WHILE
select * from #dmBOM_expanded
order by item
------------------------------
-- clean up
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..#stack','U') IS NOT NULL
DROP TABLE #stack
--------------------------------
April 11, 2011 at 3:22 pm
david.moule (4/11/2011)
Using the BOL article on "Expanding Hierarchies"... I took the example code and modified it as below. My problem is that it needs the top level BOM in order to start the expansion, whereas I want the output from the expansion of all the top level BOM's in my list of BOM's....
I take it you didn't actually read what I sent you, then.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 11, 2011 at 3:26 pm
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
Change is inevitable... Change for the better is not.
April 11, 2011 at 3:38 pm
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...
April 22, 2011 at 12:45 pm
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.
April 23, 2011 at 1:04 pm
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
Change is inevitable... Change for the better is not.
April 23, 2011 at 1:10 pm
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
Change is inevitable... Change for the better is not.
June 21, 2011 at 7:47 am
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
Viewing 15 posts - 31 through 45 (of 45 total)
You must be logged in to reply to this topic. Login to reply