Select through multiple iterations

[Uncle Dickie]

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!

[ck9663]

Looks like you're trying to grab the first non-null value. Will COALESCE() work?

-- CK

[Uncle Dickie]

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

[ck9663]

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

[Uncle Dickie]

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