Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Recursive query for a Bill of Materials table Expand / Collapse
Author
Message
Posted Friday, April 1, 2011 3:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 3:52 AM
Points: 82, Visits: 412
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
Post #1087318
Posted Friday, April 1, 2011 7:22 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 36,941, Visits: 31,443
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
"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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1087447
Posted Friday, April 1, 2011 12:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 36,941, Visits: 31,443
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1087653
Posted Tuesday, April 5, 2011 4:43 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 3:52 AM
Points: 82, Visits: 412
Hi Jeff,

I can't see any spam in my junk mail... or anything in my PM box....

Post #1088970
Posted Tuesday, April 5, 2011 5:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 36,941, Visits: 31,443
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1088975
Posted Thursday, April 7, 2011 11:19 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 3:52 AM
Points: 82, Visits: 412
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?

Post #1090091
Posted Thursday, April 7, 2011 2:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 36,941, Visits: 31,443
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1090242
Posted Monday, April 11, 2011 2:32 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 3:52 AM
Points: 82, Visits: 412
Hi Jeff,

I got your .net email and have sent a test reply to it.....

Post #1091753
Posted Monday, April 11, 2011 2:39 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 3:52 AM
Points: 82, Visits: 412
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
--------------------------------


Post #1091760
Posted Monday, April 11, 2011 3:22 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 36,941, Visits: 31,443
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1091787
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse