x
Solved

exploded bill of materials

Posted on 2015-02-04
Medium Priority
129 Views
I have a table with bill of material information.  I would like to be able to generate a report that shows for each Parent_Part_Num, all of the sub components and any of those sub components that have sub components.

For example:

Parent_Part_Num

123456-001
.............Lvl1....222222-001
....................Lvl2....333333-001
....................Lvl2....444444-001
.............Lvl1....555555-001
.............Lvl1....666666-001

My bill of material source table (BOM_RAW) is structured like this.

Parent_Part_Num      Child_Part_Num
123456-001                555555-001
123456-001                666666-001
222222-001                333333-001
222222-001                444444-001

Is this type of report possible?
0
Question by:Fairfield
• 8
• 5

LVL 75

Expert Comment

ID: 40589539
are you sure the data you provided is correct ?
0

Author Comment

ID: 40589553
Yes, it is for example only.
0

Author Comment

ID: 40589629
The report could even be like this.

PARENT_PART_NUM         LEVEL         CHILD
123456-001                       01               222222-001
123456-001                       01               333333-001
123456-001                       02               444444-001
123456-001                       02               555555-001
123456-001                       03               666666-001
0

LVL 75

Accepted Solution

Aneesh Retnakaran earned 2000 total points
ID: 40589716
CREATE TABLE toDelete (Parent_Part_Num  VARCHAR(100), Child_Part_Num VARCHAR(100) )
INSERT INTO dbo.toDelete
( Parent_Part_Num, Child_Part_Num )
VALUES  ('123456-001','222222-001'), ('123456-001','555555-001'), ('222222-001','333333-001'), ('222222-001','444444-001')
INSERT INTO dbo.toDelete VALUES  ('444444-001','888888-001')
;WITH MyCTE
AS ( SELECT Parent_Part_Num, Child_Part_Num, levl = 1
FROM toDelete t
WHERE NOT EXISTS (SELECT 1 FROM toDelete t1 WHERE t.Parent_Part_Num = t1.Child_Part_Num)
UNION ALL
SELECT MyCTE.Parent_Part_Num, e.Child_Part_Num, MyCTE.levl+1
FROM toDelete e
INNER JOIN MyCTE ON e.Parent_Part_Num = MyCTE.Child_Part_Num
--WHERE Employee.ManagerID IS NOT NULL
)
SELECT *
FROM MyCTE
0

Author Comment

ID: 40589722
Thank you for responding.  Do I have specifically call out the parent and child numbers?  Is it just possible to have the output look like the following?

Parent_Part Number     Level       Child_Part_Num
0

LVL 75

Expert Comment

ID: 40589823
change the last statement to this

SELECT Parent_Part Number, levl as [Level] ,Child_Part_Num
FROM MyCTE
0

Author Comment

ID: 40589824
All this seems to give me is everything that is level 1, I was hoping to have multiple levels for Parent_Part_Num
0

LVL 75

Expert Comment

ID: 40589829
can you export your data into an excel file and post it here; with the sample data, I am getting levels up to 3
0

Author Comment

ID: 40589842
This works great.
0

Author Comment

ID: 40589900
I am getting an error

The statement terminated. The maximum recursion 100 has been exhausted before statement completion
0

LVL 75

Assisted Solution

Aneesh Retnakaran earned 2000 total points
ID: 40589966
SELECT Parent_Part Number, levl as [Level] ,Child_Part_Num
FROM MyCTE
option (maxrecursion 0)
0

Author Comment

ID: 40594708
You are right, I selected the wrong portion of the solution.  How do I reopen?
0

Author Comment

ID: 40594718
I don't know if I need to create a new question but how would I use a where statement to only find BOM's from a input table to join Parent_Part_Number?
0

Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.