Solved

exploded bill of materials

Posted on 2015-02-04
15
90 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

758 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now