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!
4 1822
Looks like you're trying to grab the first non-null value. Will COALESCE() work?
-- CK
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
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
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
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...
| |