June 21, 2011 at 3:22 pm
Not sure if it's relevant now, but in case... here's my code for an example where a list of Top Bills of Materials is used and a product structure table, to create a BOM explosion.
(Thanks also go to Jeff M during its birth):
------------------------------------------------------------------------
-- 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
June 21, 2011 at 7:17 pm
Hi GSquared,
Are there any guidelines for an editorial as opposed to a post?
Mike
June 22, 2011 at 8:13 am
MikeAngelastro-571287 (6/21/2011)
Hi GSquared,Are there any guidelines for an editorial as opposed to a post?
Mike
Check the "Write for us" link on the left side of the site.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2011 at 7:51 pm
Hi GSquared,
I sent a proposed article to the article email address for SQL Server Central on June 27 but haven't received any notification that it was received. How many days does it typically take to receive a status on article submissions?
Thanks,
Mike
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply