|
P: 67
|
Sorry for the slightly obscure Title. I couldn't really think of a way to describe my scenario accurately, but here goes:
The following bit of code retrieves the latest bill of materials for a part: -
SELECT b.BOMPartID, b.ComponentPartID, b.Quantity, b.ScrapPercent
-
FROM BillOfMaterials b
-
JOIN Versions v ON v.VersionID = b.VersionID
-
WHERE v.StatusID = 1
-
AND b.PrimeComponent = 1
-
My problem is that the ComponentPartID may also have its own bill of materials and I want to end up with just a single list of all components required on the chosen part.
I have tried using multiple LEFT JOINs for the BillOfMaterials table (where the ComponentPartID in the first table is linked to the BOMPartID in the second copy of the table) and I almost get what I want. -
SELECT b1.BOMPartID,
-
CASE
-
WHEN b7.ComponentPartID IS NOT NULL THEN b7.ComponentPartID
-
WHEN b6.ComponentPartID IS NOT NULL THEN b6.ComponentPartID
-
WHEN b5.ComponentPartID IS NOT NULL THEN b5.ComponentPartID
-
WHEN b4.ComponentPartID IS NOT NULL THEN b4.ComponentPartID
-
WHEN b3.ComponentPartID IS NOT NULL THEN b3.ComponentPartID
-
WHEN b2.ComponentPartID IS NOT NULL THEN b2.ComponentPartID
-
END
-
FROM BillOfMaterials b1
-
LEFT JOIN BillOfMaterials b2 ON b2.BOMPartID = b1.ComponentPartID
-
LEFT JOIN BillOfMaterials b3 ON b3.BOMPartID = b2.ComponentPartID
-
LEFT JOIN BillOfMaterials b4 ON b4.BOMPartID = b3.ComponentPartID
-
LEFT JOIN BillOfMaterials b5 ON b5.BOMPartID = b4.ComponentPartID
-
LEFT JOIN BillOfMaterials b6 ON b6.BOMPartID = b5.ComponentPartID
-
LEFT JOIN BillOfMaterials b7 ON b7.BOMPartID = b6.ComponentPartID
-
but this leaves me with lots of seemingly repeated code (which I am sure should not be necessary) and another problem:
The number of sub assemblies that exist may vary from 0 to ??. From running through the LEFT JOIN I can see that currently there are up to 6 levels but there is nothing to stop the bill of materials having any number.
I also can't get the 'WHERE' conditions working in this version but I have not tried many things as I don't think this is the way I should be doing it anyway.
Any ideas on how to achieve what I am after would be much appreciated!
| |
Share this Question
| Expert 2.5K+
P: 2,878
|
Looks like you're trying to grab the first non-null value. Will COALESCE() work?
-- CK
| |
|
P: 67
|
The problem is more with getting the full BOM e.g. table contains the following
BomPartID, ComponentPartID, StatusID, PrimeComponent
PartA, PartB, 1, 1
PartA, PartC, 1, 1
PartA, PartD, 1, 0
PartB, PartE, 1, 1
PartB, PartF, 2, 1
PartE, PartG, 1, 1
PartF, PartH, 1, 1
Result set wanted is (linking it to a Part Master table so all parts are included even if they are not in the BOM table):
PartA, PartB
PartA, PartC
PartA, PartE (because it is a sub component of PartB)
PartA, PartG (because it is a sub component of PartE)
PartB, PartE
PartC, Null (no sub components)
PartD, Null
PartE, PartG
PartF, PartH
The entries:
PartA, PartD would not exist as PartD is not a PrimeComponent
PartB, PartF would not exist because it is not the 'current' version
| |
| Expert 2.5K+
P: 2,878
|
A Common Table Expression might be helpful for this requirement. Something I'm thinking of creating a sample but seems to not have the time to do, sorry :)
-- CK
| |
|
P: 67
|
Thanks so much for the pointer.
A Common Table Expression might be helpful
I found a good starting point here.
Having read that I have come up with code as follows which is pretty much there - need to tweak it a little still but that should be fine - WITH MyBOM (BOMPartID, ComponentPartID, Quantity, ScrapPercent, Level)
-
AS
-
(
-
SELECT b.BOMPartID, b.ComponentPartID, b.Quantity, b.ScrapPercent, 1 AS Level
-
FROM Structure.BillOfMaterials b
-
JOIN Structure.Versions v ON v.VersionID = b.VersionID
-
WHERE v.StatusID = 1
-
AND b.PrimeComponent = 1
-
-- AND b.BOMPartID = 192
-
UNION ALL
-
SELECT b.BOMPartID, b.ComponentPartID, b.Quantity, b.ScrapPercent, Level + 1
-
FROM Structure.BillOfMaterials b
-
JOIN Structure.Versions v ON v.VersionID = b.VersionID
-
INNER JOIN MyBOM m ON b.BOMPartID = m.ComponentPartID
-
WHERE v.StatusID = 1
-
AND b.PrimeComponent = 1
-
)
-
SELECT p1.PartNumber, p2.PartNumber, Quantity, ScrapPercent, Level
-
FROM myBOM m
-
LEFT JOIN Structure.Parts p1 ON p1.PartID = m.BOMPartID
-
LEFT JOIN Structure.Parts p2 ON p2.PartID = m.ComponentPartID
-
ORDER BY m.Level
| | | |  Question stats - viewed: 1411
- replies: 4
- date asked: Jan 14 '09
|
