Solved

exploded bill of materials

Posted on 2015-02-04
15
110 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

738 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