470,575 Members | 1,240 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,575 developers. It's quick & easy.

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:

Expand|Select|Wrap|Line Numbers
  1. SELECT  b.BOMPartID, b.ComponentPartID, b.Quantity, b.ScrapPercent
  2. FROM    BillOfMaterials b
  3. JOIN    Versions v ON v.VersionID = b.VersionID
  4. WHERE   v.StatusID = 1
  5. 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.

Expand|Select|Wrap|Line Numbers
  1. SELECT   b1.BOMPartID,
  2.          CASE 
  3.            WHEN b7.ComponentPartID IS NOT NULL THEN b7.ComponentPartID  
  4.            WHEN b6.ComponentPartID IS NOT NULL THEN b6.ComponentPartID 
  5.            WHEN b5.ComponentPartID IS NOT NULL THEN b5.ComponentPartID 
  6.            WHEN b4.ComponentPartID IS NOT NULL THEN b4.ComponentPartID 
  7.            WHEN b3.ComponentPartID IS NOT NULL THEN b3.ComponentPartID 
  8.            WHEN b2.ComponentPartID IS NOT NULL THEN b2.ComponentPartID
  9.           END
  10. FROM         BillOfMaterials b1 
  11. LEFT JOIN    BillOfMaterials b2 ON b2.BOMPartID = b1.ComponentPartID
  12. LEFT JOIN    BillOfMaterials b3 ON b3.BOMPartID = b2.ComponentPartID
  13. LEFT JOIN    BillOfMaterials b4 ON b4.BOMPartID = b3.ComponentPartID
  14. LEFT JOIN    BillOfMaterials b5 ON b5.BOMPartID = b4.ComponentPartID
  15. LEFT JOIN    BillOfMaterials b6 ON b6.BOMPartID = b5.ComponentPartID
  16. 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!
Jan 14 '09 #1
4 1571
2,878 Expert 2GB
Looks like you're trying to grab the first non-null value. Will COALESCE() work?

-- CK
Jan 14 '09 #2
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
Jan 15 '09 #3
2,878 Expert 2GB
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
Jan 16 '09 #4
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

Expand|Select|Wrap|Line Numbers
  1. WITH MyBOM (BOMPartID, ComponentPartID, Quantity, ScrapPercent, Level)
  2. AS
  3. (
  4.     SELECT    b.BOMPartID, b.ComponentPartID, b.Quantity, b.ScrapPercent, 1 AS Level
  5.     FROM    Structure.BillOfMaterials b
  6.     JOIN    Structure.Versions v ON v.VersionID = b.VersionID
  7.     WHERE    v.StatusID = 1
  8.     AND        b.PrimeComponent = 1
  9. --    AND        b.BOMPartID = 192
  10.     UNION ALL
  11.     SELECT    b.BOMPartID, b.ComponentPartID, b.Quantity, b.ScrapPercent, Level + 1
  12.     FROM    Structure.BillOfMaterials b
  13.     JOIN    Structure.Versions v ON v.VersionID = b.VersionID
  14. INNER JOIN    MyBOM m ON b.BOMPartID = m.ComponentPartID
  15.     WHERE    v.StatusID = 1
  16.     AND        b.PrimeComponent = 1
  17. )
  18. SELECT        p1.PartNumber, p2.PartNumber, Quantity, ScrapPercent, Level
  19. FROM        myBOM m
  20. LEFT JOIN    Structure.Parts p1 ON p1.PartID = m.BOMPartID
  21. LEFT JOIN    Structure.Parts p2 ON p2.PartID = m.ComponentPartID
  22. ORDER BY    m.Level
Jan 17 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Rolf Wester | last post: by
1 post views Thread by Vikas Vijay | last post: by
6 posts views Thread by Ben Hallert | last post: by
2 posts views Thread by areef.islam | last post: by
1 post views Thread by abhishekhs | last post: by
10 posts views Thread by charlie.xia.fdu | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.