472,992 Members | 3,413 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,992 software developers and data experts.

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

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

Similar topics

by: Rolf Wester | last post by:
Hi, I have a form with a select element with multiple="true". When using the GET method (I suppose the same happens with the POST method) I can seen that the form sends channels=CH1&channels=CH2...
by: Vikas Vijay | last post by:
hello all, I have a c++ code in which is sth like while(i < N) { .... do some processing in c++ to create file abc system("simulator_name < abc") ......c++ ... processing...... }
by: Ben Hallert | last post by:
Hi guys, I'm trying to figure out what bone headed mistake I made on something I put together. I've got a form (named 'context') that has a variable number of select-multiple inputs on it. ...
by: Bonge Boo! | last post by:
This has got to be obvious, but I can't make it work. I have a form called with 3 pull down menus. They are linked to a database which generates the values for the <SELECT? Pull-downs. Lets...
by: areef.islam | last post by:
Hi, I am kinda new to javascript and I am having this problem with selecting multiple options from a select tag. Hope someone can help me out here. here is my code...
by: abhishekhs | last post by:
Hi all I have more than one multiple select lists in a page. Something like this <tr> <td> <select NAME="StrainList" ID="StrainList" SIZE="5" multiple="multiple" style="width: 150px"> <?...
by: John A Grandy | last post by:
Is it possible to write a foreach so that it simultaneously iterates through two collections ( or two properties of the same collection ) ? Following is *only meant as an example* : -- iterate...
by: charlie.xia.fdu | last post by:
Hi C++ users, for(int i=0,int j=10 ; i<5&&j<10 ; i++ , j--) {} example from: http://www.tech-faq.com/iterations.shtml Is not valid in my eclipse cdt. Is there multiple initialization in C++?...
by: phpnewbie26 | last post by:
My current form has one multiple select drop down menu as well as few other drop down menus that are single select. Originally I had it so that the multiple select menu was first, but this created...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.