Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

exploded bill of materials

Posted on 2015-02-04
15
Medium Priority
?
120 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 75

Accepted Solution

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

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 2000 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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

705 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