Billing of Materials query

Question

Post reply

Billing of Materials query

cidr

Ten Centuries

Points: 1353
More actions
September 19, 2012 at 2:29 pm

#264586

Hi there,
I'd be grateful if someone could help:)
I have a BoM issue.
The table below shows the components, A's being the highest level component B's are child components and C child components of B.. etc.
The table has two columns Parent and Child. Each parent can have many components and there can be up to 15 levels. (to keep it simple, I have given 4 levels)
Parent Child
A1 B1
A1 B2
A1 B3
B1 C1
B1 C2
B1 C3
C1 D1
C1 D2
D1 E1
D1 E2
A2 B4
A2 B5
B5 C4
In this table A1, is related to B1, B2, B3 and indirectly related to all components within the top ten rows. It then goes to A2, to make this simple, none of the rows and indirect rows are related to the top ten rows.
What I want to attempt to produce is query with a Parent (A) parameter. When selected should look like this:
select Material from BoM where parent = 'A1' (purely just an example select for brevity)
Result
B1
B2
B3
C1
C2
C3
D1
D2
E1
E2
This is basically a list of all the components related directly or indirectly to A1.
The reason to do this is to list Quantities and purchase costs which I'll add later. As long as I can create this column of related components I'll be able to work out the rest.
Does anyone have a good solution for this?
Any help is very much appreciated.
Thanks

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

ok, i think this recursive CTE generates allt eh sub parts you are looking for:

DECLARE @Table Table(

Parent varchar(2),

Child varchar(2) )

INSERT @Table

SELECT 'A1','B1' UNION ALL

SELECT 'A1','B2' UNION ALL

SELECT 'A1','B3' UNION ALL

SELECT 'B1','C1' UNION ALL

SELECT 'B1','C2' UNION ALL

SELECT 'B1','C3' UNION ALL

SELECT 'C1','D1' UNION ALL

SELECT 'C1','D2' UNION ALL

SELECT 'D1','E1' UNION ALL

SELECT 'D1','E2' UNION ALL

SELECT 'A2','B4' UNION ALL

SELECT 'A2','B5' UNION ALL

SELECT 'B5','C4';

WITH Dependencies AS(

SELECT Parent, Child

FROM @Table

WHERE Parent = 'A1'

UNION ALL

SELECT t.Parent, t.Child

FROM @Table t

JOIN Dependencies d ON t.Parent = d.Child

)

SELECT Child

FROM Dependencies

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

cidr Ten Centuries Points: 1353 More actions · Answer 2

Thanks for responding Lowell,

The only issue with this technique is that there are literally 10s of thousands of parts so I'm not sure if this technique would work; I wont be able to hard code the values for this reason.

Lowell SSC Guru Points: 323504 More actions · Answer 3

cidr (9/19/2012)
Thanks for responding Lowell,
The only issue with this technique is that there are literally 10s of thousands of parts so I'm not sure if this technique would work; I wont be able to hard code the values for this reason.

because you did not provide any table names, i had to generate the data based on what you posted.

by obfuscating everything to A1,A2 parent/Child, you lost the ability to get an answer you can translate over easily.

if you substitute 4 things in the example i provided, it will work with your data:

@Table <--> change to the real table name

Parent <-->real column name

child <--> real column name

WHERE Parent = 'A1' <-->change 'A1' to the real value

WITH Dependencies AS(

SELECT Parent, Child

FROM @Table

WHERE Parent = 'CAR'

UNION ALL

SELECT t.Parent, t.Child

FROM @Table t

JOIN Dependencies d ON t.Parent = d.Child

)

SELECT Parent,Child

FROM Dependencies

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Lowell (9/19/2012)
cidr (9/19/2012)
Thanks for responding Lowell,
The only issue with this technique is that there are literally 10s of thousands of parts so I'm not sure if this technique would work; I wont be able to hard code the values for this reason.
because you did not provide any table names, i had to generate the data based on what you posted.
by obfuscating everything to A1,A2 parent/Child, you lost the ability to get an answer you can translate over easily.
if you substitute 4 things in the example i provided, it will work with your data:
@Table <--> change to the real table name
Parent <-->real column name
child <--> real column name
WHERE Parent = 'A1' <-->change 'A1' to the real value
WITH Dependencies AS(
SELECT Parent, Child
FROM @Table
WHERE Parent = 'CAR'
UNION ALL
SELECT t.Parent, t.Child
FROM @Table t
JOIN Dependencies d ON t.Parent = d.Child
)
SELECT Parent,Child
FROM Dependencies

And I thought there was no spoon. 😉

cidr Ten Centuries Points: 1353 More actions · Answer 5

Lowell 2012-09-12
by obfuscating everything to A1,A2 parent/Child, you lost the ability to get an answer you can translate over easily.

Ah, I'm sorry Lowell, my bad, I've been zoned out all day I didn't realise that the values weren't hard coded and that the code was creating a table variable.

Sorry about that, you're answer looks good.

Thanks so much

Lowell SSC Guru Points: 323504 More actions · Answer 6

Lynn Pettis (9/19/2012)
And I thought there was no spoon. 😉

@67 posts, i didn't think this was homework for the OP, and i got all excited about making a recursive CTE work before someone else beat me to to it.

spoon fed answers, yeah, guilty as charged.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

cidr Ten Centuries Points: 1353 More actions · Answer 7

lowell 2012-09-12
@67 posts, i didn't think this was homework for the OP, and i got all excited about making a recursive CTE work before someone else beat me to to it.

Haha, I'm sorry but I do not know what this means; what's an OP? :w00t:

Lowell SSC Guru Points: 323504 More actions · Answer 8

cidr (9/19/2012)
lowell 2012-09-12
@67 posts, i didn't think this was homework for the OP, and i got all excited about making a recursive CTE work before someone else beat me to to it.
Haha, I'm sorry but I do not know what this means; what's an OP? :w00t:

lol just shorthand for "Original Poster".

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

cidr Ten Centuries Points: 1353 More actions · Answer 9

Lowell 2012-09-12
lol just shorthand for "Original Poster".

Er, I'll take that as a compliment.

I really have been out of touch for the last year 😀

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

CELKO (9/20/2012)
... I got gigged on that when I did my first BOM job.

I don't think anyone really cares, except maybe you and your pet mouse.

aaron.reese SSChampion Points: 13498 More actions · Answer 11

It is Called Adjency List model and it is very common in Relational databases

Organisation hierachy, BOMs and Menu structures are three uses for it. Intil SQL2005 came along it was a PITA to programme.

Celcko won't like it because recursive CTEs are a Microsoft specific extension to the SQL syntax. By all means read his book (or any other posts on the web about it), but if you are working in SQL2005 or higher, then use CTEs.

Before anyone says what about portability - really - how often do you port database from one platform to another.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 12

CELKO (9/20/2012)
It how non-SQL programmers mimicked the pointer chains in SQL that they knew from assembly language and old network databases.

I wonder if Dr. Codd would take exception to that since he was one of the first to propose it.

--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)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 13

aaron.reese (9/20/2012)
It is Called Adjency List model and it is very common in Relational databases
Organisation hierachy, BOMs and Menu structures are three uses for it. Intil SQL2005 came along it was a PITA to programme.
Celcko won't like it because recursive CTEs are a Microsoft specific extension to the SQL syntax. By all means read his book (or any other posts on the web about it), but if you are working in SQL2005 or higher, then use CTEs.
Before anyone says what about portability - really - how often do you port database from one platform to another.

If you consider the fact that Celko uses a 1950s push-stack and While loop to convert Adjacency Lists to Nested Sets, you can certainly understand his dislike for Adjacency Lists. 😛

--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)

Smash125 Hall of Fame Points: 3562 More actions · Answer 14

Smash125

Hall of Fame

Points: 3562

September 21, 2012 at 3:43 am

#1540547