Solved

exploded bill of materials

Posted on 2015-02-04
15
105 Views
Last Modified: 2016-06-18
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
Comment
Question by:Fairfield
  • 8
  • 5
15 Comments
 
LVL 75

Expert Comment

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

Author Comment

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

Author Comment

by:Fairfield
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 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

by:Fairfield
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

by:Aneesh Retnakaran
ID: 40589823
change the last statement to this

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

Author Comment

by:Fairfield
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

by:Aneesh Retnakaran
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

by:Fairfield
ID: 40589842
This works great.
0
 

Author Comment

by:Fairfield
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

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

Author Comment

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

Author Comment

by:Fairfield
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql query help 15 51
Need sql in string 2 28
Need help with a query 14 35
SQL syntax question 6 38
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question