RE: Recursive User-Defined Function

Default port

Points: 1485

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