T-SQL Recursion in SQL 2000
-
I have Bill of Material table which can have n-levels.
I want to create a view that shows the fully exploded part list for any finished good.
The tables that hold this information are:
BOMMAST
- FINISHED_GOOD_CODE
BOMTRAN
- FINISHED_GOOD_CODE
- BILL_LINE_NUMBER
- STOCK_CODE
- FIXEDVARIABLE
- QUANTITY
My problem is that the ttock code on the BOMTRAN can be a manufactured item and I need the query to drill down further to show the parts of this level.
What is my best approach?
-
Look at the topic "expanding hierarchies" on Books on Line. I have adapted this to my particular problem myself and it works well.
HTH
Dave J
-
And, don't use recursion to solve this... SQL Server 2000 cannot go "N" levels... it's limited to only 32 levels and then "BOOOOOOM!"
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks for the advice, now reading through the many "Expanding Hierachies" articles.
Looks a lot more promising.
-
The one David was talking about is actually available in "Help" in Query Analyzer... it's also known as "Books OnLine".
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Good catch Jeff.
Sorry Shane, I should have said "look *in* Books Online"
Dave J
-
Thanks guys,
I did understand what you meant and found the article and then did some further research on what other comments were available on the subject.
What I have come up with so far is:
ALTER PROCEDURE dbo.SP_CASE_Explode_BOM (@item CHAR(15)) AS
SET NOCOUNT ON
DECLARE @lvl int,
@master_item char(15)
SET @master_item = @item
CREATE TABLE #stack (
item char(15),
lvl int)
CREATE TABLE #stack2 (
master_item char(15),
bill_header_item char(15) ,
bill_item char(15),
bill_line int,
bill_qty numeric(12,4),
fixed_or_variable char(1),
bottom_level char(1))
INSERT INTO #stack (
item,
lvl)
VALUES (
@item,
1)
SELECT
@lvl = 1
WHILE @lvl > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE lvl = @lvl)
BEGIN
SELECT
@item = item
FROM
#stack
WHERE
lvl = @lvl
DELETE FROM
#stack
WHERE
lvl = @lvl AND
item = @item
INSERT
#stack
SELECT
STOCK_CODE,
@lvl + 1
FROM
BOMTRAN
WHERE
BILL_ORDER_CODE = @item
INSERT
#stack2
SELECT
@master_item,
BOMT.BILL_ORDER_CODE,
BOMT.STOCK_CODE,
BOMT.BILL_ORDER_LINE,
BOMT.BILL_ORDER_QTY,
BOMT.FIXED_VARIABLE,
CASE
WHEN EXISTS(SELECT BOMM.BILL_ORDER_CODE FROM BOMMAST BOMM WHERE BOMM.BILL_ORDER_CODE = BOMT.STOCK_CODE AND BOMM.BILL_ORDER = 'B')
THEN 'N'
ELSE 'Y'
END
FROM
BOMTRAN BOMT
WHERE
BOMT.BILL_ORDER_CODE = @item AND
BOMT.BILL_ORDER = 'B'
IF @@ROWCOUNT > 0
BEGIN
SELECT @lvl = @lvl + 1
END
END
ELSE
BEGIN
SELECT @lvl = @lvl - 1
END
END
SELECT
*
FROM
#stack2
WHERE
bottom_level = 'Y'
Can I incorporate the results into a View?
-
No... but you could place the results of the proc in a temp table...
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply