[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 698
  • Last Modified:

bom where used

I have two tables, one that consists of a parts list and the other that contains bom information with parent and child.  In need to take the list of parts and find all of the parent part numbers where the parts list exist. The parts from the parts list table could be in parent part numbers or could be in higher assemblIes.   How would I do this?

Parts List Table
------------------
PartID


BOM Table
----------      ------------
ParentID      ChildPartID
0
Fairfield
Asked:
Fairfield
  • 4
  • 3
  • 2
  • +1
1 Solution
 
Daniel WilsonCommented:
This is a classic hierarchy problem.  In SQL 2005 & 2008 we got the CTE that can be recursive ... and can help with such things.

http://www.eggheadcafe.com/articles/sql_server_recursion_with_clause.asp

If that's not enough help, I'll try to write you some code!
0
 
FairfieldAuthor Commented:
If you could write some sample code, I would appreciate it.
0
 
Chris LuttrellSenior Database ArchitectCommented:
Can you show the complete table structure of the Parts List table, are there columns to indicate Head Assemblies or sub-assemblies and such?  The question when you want to do recurrsion is where are you starting, you either have to pick a part and work up to the parent(s) or start with the top Assembly and work down to all the subs and parts.  I am not sure what you are wanting yet.  To "find all of the parent part numbers where the parts list exist" I read that as finding all parts that are parents and have sub-parts and you can get that with something like this:
      Select *
      From [Parts List Table]
      Where PartId in (Select ParentId From [BOM Table])
That will return all Parts that are Parents but that may include Sub-Assemblies that are components of other Parts and not necessarily stand alone.
Does that make any sense?  I am just trying to get to the root of your question.
It may also help to provide a small sample of data that either I or Daniel could use to help provide example code.
HTH,
Chris
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
Daniel WilsonCommented:
Here are the tables with which I'm working.  Am I close?

I'm working on the CTE query ...

Create Table dbo.Parts
( PartID int)
go

Create Table dbo.BOM
( ParentID int,
	ChildPartID int)
go

Insert Into Parts(PartID) Values(1)	
Insert Into Parts(PartID) Values(2)
Insert Into Parts(PartID) Values(3)
Insert Into Parts(PartID) Values(4)
Insert Into Parts(PartID) Values(5)
Insert Into Parts(PartID) Values(6)
Insert Into Parts(PartID) Values(7)
Insert Into Parts(PartID) Values(8)
Insert Into Parts(PartID) Values(9)
Insert Into Parts(PartID) Values(10)
Insert Into Parts(PartID) Values(11)	
Insert Into Parts(PartID) Values(12)
Insert Into Parts(PartID) Values(13)
Insert Into Parts(PartID) Values(14)
Insert Into Parts(PartID) Values(15)
Insert Into Parts(PartID) Values(16)
Insert Into Parts(PartID) Values(17)
Insert Into Parts(PartID) Values(18)
Insert Into Parts(PartID) Values(19)

go

Insert Into BOM(ParentID, ChildPartID) Values (8,9)
Insert Into BOM(ParentID, ChildPartID) Values (8,7)
Insert Into BOM(ParentID, ChildPartID) Values (8,6)
Insert Into BOM(ParentID, ChildPartID) Values (8,5)
Insert Into BOM(ParentID, ChildPartID) Values (2,8)
go

Open in new window

0
 
Daniel WilsonCommented:
This is what I've come up with, but it's wrong.

CG, do you know these CTE's better?

with MyCTE (Parent, Child)
AS
(
	Select P.PartID, B.ChildPartID from Parts P left Join BOM B on P.PartID = B.ParentID
		--Where B.ParentID is NULL
	UNION ALL
	Select P.PartID, B.ChildPartID from Parts P Inner Join BOM B on P.PartID = B.ParentID
	Inner Join MyCTE as C on B.ChildPartID = C.Child
)
Select * from MyCTE Where Child =9

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
OK, please have a look at the following....

Borrowed the create of the *testing purposes only* tables (except made them temp tables) from DanielWilson (thanks mate) see below in code snippet.

First up if just checking to see which Parts are in the BOM table, then just do :

SELECT * FROM #parts WHERE partid in (select distinct parentid from #bom union select distinct childpartid from #bom)

Now the CTE query is a lot more fun, so will also play with that... It creates a couple of additional columns - indented_tree and tree_path just to visualise the relationships.

;WITH BOM_TREE AS
(--Base Query
SELECT 1 AS Level,
convert(varchar(8000),right('000000'+convert(varchar,ParentID),6)) as Tree_Path,
convert(varchar(8000),right('000000'+convert(varchar,ParentID),6)) as Indented_Tree,
Parentid as PartID,
convert(int, NULL) as Parentid
From #BOM B
where not exists (select NULL from #BOM C where B.parentid = C.childpartid)

UNION ALL -- recursive bit

SELECT Level + 1,
tree_path + right('000000'+convert(varchar,B.ChildPartID),6), -- zero fills a 6 digit code
replicate(' ',Level+1) + convert(varchar(8000),right('000000'+convert(varchar,B.ChildPartID),6)) as Indented_Tree,
B.ChildPartID,
B.ParentID
FROM BOM_TREE T
JOIN #BOM B ON B.Parentid = T.PartID

) --Final Select
SELECT B.*
FROM BOM_TREE B
INNER JOIN #parts P on B.PartID = P.PartID
ORDER BY Tree_path
-- more like a tree - makes path_tree_name more realistically indented
-- check it out if you order by level and partid - doesn't show relationship as a tree.

0
 
FairfieldAuthor Commented:
so here is my actual tables with sample data, please help

tbl802_parts (table)

PART_NUM
C1710-80127      
C5683-23040      
C8264A            
CB001A      


vBOM (table)    

PARENT_PART_NUM      USAGE      SITE_ID      CHILD_PART_NUM
AS895US#ABA             1                KW944AV#ABA      
AS895US#ABA             1                GW678AV          
AS895US#ABA             1                FH032AV#ABA      
AS895US#ABA             1                FH631AV          
AS895US#ABA             1                FJ659AV#ABA      
0
 
Mark WillsTopic AdvisorCommented:
Well, that is interesting because no PART_NUM exists in vBOM

as shown by the query :

SELECT * FROM tbl802_parts WHERE part_num in (select distinct parent_part_num from vbom union select distinct child_part_num from vbom)

Which is probably better structured as :

SELECT * FROM tbl802_parts WHERE exists (select NULL from vbom where part_num in (parent_part_num,child_part_num))

or even :

SELECT distinct tbl802_parts.part_num  
FROM tbl802_parts
LEFT JOIN vBom P on tbl802_parts.part_num = P.Parent_part_num
LEFT JOIN vBom C on tbl802_parts.part_num = C.child_part_num
WHERE p.parent_part_num is not null or c.child_part_num is not null

But as for the CTE, there doesnt seem to be the requirement if all we need to do is to check the existance of part_num in the vBom. Maybe you could clarify ?



0
 
Daniel WilsonCommented:
Thanks for jumping in on this, Mark.  The CTE was giving me fits!
0
 
Mark WillsTopic AdvisorCommented:
No worries Daniel, but me thinks it might not be jist a CTE problem, beginning to think it is "show me parts that are used (anywhere) in bom", or maybe the BOM has entries that no longer exists, either way, I think there is more to it...

And while I think about it, we can also find parts used in BOM via :

SELECT distinct tbl802_parts.part_num  
FROM tbl802_parts
INNER JOIN vBom P on tbl802_parts.part_num in (P.Parent_part_num,P.child_part_num)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now