exploded bill of materials
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....22222
....................Lvl2..
....................Lvl2..
.............Lvl1....55555
.............Lvl1....66666
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?
For example:
Parent_Part_Num
123456-001
.............Lvl1....22222
....................Lvl2..
....................Lvl2..
.............Lvl1....55555
.............Lvl1....66666
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?
Aneesh
are you sure the data you provided is correct ?
ASKER
Yes, it is for example only.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Parent_Part Number Level Child_Part_Num
change the last statement to this
SELECT Parent_Part Number, levl as [Level] ,Child_Part_Num
FROM MyCTE
SELECT Parent_Part Number, levl as [Level] ,Child_Part_Num
FROM MyCTE
ASKER
All this seems to give me is everything that is level 1, I was hoping to have multiple levels for Parent_Part_Num
can you export your data into an excel file and post it here; with the sample data, I am getting levels up to 3
ASKER
This works great.
ASKER
I am getting an error
The statement terminated. The maximum recursion 100 has been exhausted before statement completion
The statement terminated. The maximum recursion 100 has been exhausted before statement completion
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You are right, I selected the wrong portion of the solution. How do I reopen?
ASKER
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?