SQL Clone
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
dave hants
dave hants
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 Visits: 542
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87248 Visits: 41113
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. :-P

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

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87248 Visits: 41113
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

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

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

Group: General Forum Members
Points: 153 Visits: 542
Hi Jeff,

I can't see any spam in my junk mail... or anything in my PM box....
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87248 Visits: 41113
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

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

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

Group: General Forum Members
Points: 153 Visits: 542
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?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87248 Visits: 41113
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

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

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

Group: General Forum Members
Points: 153 Visits: 542
Hi Jeff,

I got your .net email and have sent a test reply to it.....
dave hants
dave hants
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 Visits: 542
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
--------------------------------
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87248 Visits: 41113
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

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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