How to return lowest level parts from BOM
-
i am trying to sort out a recursive CTE that expands a bill of Materials. I am trying to get just the lowest level parts to create a Shopping List. The cte works okay but how do I return just the lowest level parts and not the intermediate parts?
-
Add hierarchy level to your CTE and base your selection on that?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
That’s what I was thinking. Get max of level and return all parts having that same level.
Makes sense! Thanks!
-
As long as max level is the same for all of the lowest-level items, that is. Otherwise, it gets trickier.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
A row with no children means a row to which no other rows point (That row's ID is not stored in any other row's 'Parent ID'). If you're only after the bottom level and don't need to run the recursive part for the different levels' details, then one query can get right to the bottom.
SELECT ...
FROM [MyTable] AS [BottomLevel]
WHERE NOT EXISTS (SELECT * FROM [MyTable] AS [OtherLevels] WHERE [OtherLevels].ParentID = [BottomLevel].ID)
Eddie Wuerch
MCM: SQL -
Please Google the nested set model for treason hierarchies. You're still writing procedural code with traversal instead of thinking in terms of declarative set-oriented code. Finding leaf nodes in the nested set model is a trivial join.
Please post DDL and follow ANSI/ISO standards when asking for help.
-
I need to do this but have not done so yet. We have varying levels of subassembly within the same top level bill of materials so I was planning to use ISNULL(Parent.StockCode, Child.StockCode) so as to get the Child StockCode except when there was no child.
-
Just to explain it to myself or visualize what's going on, I drew a tree of parts with assemblies etc. I started with something I could get my head around and count the parts, so I drew a spinner and basically decomposed it into a frame with 4 "spinner wheels" (the whole "wheel" assembly). What I realized is that looks like you can go down the "tree" and multiply the parts in the parent by the number of parts in the next child and when you get to the bottom-most "leaf", you add the totals for each leaf and that's the number of parts. Might sound silly, but at least it makes a bit more sense now. So I go down each branch of the tree, multiplying by the quantity of a part each time until I reach the bottom of the tree, and then append the bottom level (Item, Qty) to a table, and then do something like a simple totals query on the leaf level, grouping by PartID. I think so, anyway...
- pietlinden wrote:
Just to explain it to myself or visualize what's going on, I drew a tree of parts with assemblies etc. I started with something I could get my head around and count the parts, so I drew a spinner and basically decomposed it into a frame with 4 "spinner wheels" (the whole "wheel" assembly). What I realized is that looks like you can go down the "tree" and multiply the parts in the parent by the number of parts in the next child and when you get to the bottom-most "leaf", you add the totals for each leaf and that's the number of parts. Might sound silly, but at least it makes a bit more sense now. So I go down each branch of the tree, multiplying by the quantity of a part each time until I reach the bottom of the tree, and then append the bottom level (Item, Qty) to a table, and then do something like a simple totals query on the leaf level, grouping by PartID. I think so, anyway...
Eddie Wuerch is correct... I you do a self join on the Adjacency List an find all children that do NOT appear as parents, you will have a list of all leaf-level nodes.
If you have a test BOM, post it in a readily consumable so we can get to (pun intended) the bottom of this. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
Gotta get some sleep... Just got home, so I'll do it in the morning sometime. It's a really simple tree with 3 levels, but I think it's enough to get the idea across. Gotta be awake enough to get the part <-> assembly stuff right. =) Just did it on a whiteboard and it seems to make sense... I'll double-check and finish posting in the AM.
Pieter
-
Jeff,
(and Steve, who's kinda busy this week) and anybody else... for grins, I did a PPT of this so I could see the hierarchy and understand what's going on "in the real world" a bit better. Turns out, I found it extremely helpful - even though the object I chose to "decompose" into a bill of materials was really simple. That way, you can trace each bottom level part up the chain and multiply the quantities at each level.
Would it be worthwhile to post the PPTX file? (Am I even allowed to on here... I guess if I zip it?)
Pieter
- pietlinden wrote:
Jeff,
(and Steve, who's kinda busy this week) and anybody else... for grins, I did a PPT of this so I could see the hierarchy and understand what's going on "in the real world" a bit better. Turns out, I found it extremely helpful - even though the object I chose to "decompose" into a bill of materials was really simple. That way, you can trace each bottom level part up the chain and multiply the quantities at each level.
Would it be worthwhile to post the PPTX file? (Am I even allowed to on here... I guess if I zip it?)
Pieter
You could always just screen shot it and then post as an image.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Okay, here's my screenshot. It's starting to make more sense. If I move up the hierarchy from a leaf node to the root, I just multiply the quantity at each level until I reach the root. Now to figure out how to do this in a CTE. (Or to understand the CTE for myself instead of just doing the "script kiddie" thing and using something I don't completely understand.)
I guess I should post some data (ya think?) Should I include some parts that are not part of the BOM in my tables, just to make sure I'm not oversimplifying the problem?
-
Here's an in-line version of that image.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Read that maybe 20 times before I understood. LOL Let me see if I have this right... If this were a "normal" table of component parts...
CREATE TABLE Component(ComponentID INT IDENTITY PRIMARY KEY...);
CREATE TABLE ComponentPart(ComponentID INT NOT NULL, PartID INT NOT NULL, PartQty TINYINT);
Any Component that is a bottom-level component does not appear in the domain of Component(ComponentID). Any Component that is a Top-level component (finished product) does not appear in the domain of ComponentPart(PartID), so use NOT EXISTS() to check for existence in those.
But what if the ComponentParts table is never materialized? (or is it, and I'm just not recognizing it?) Do I have to materialize the ComponentParts table by exploding the BOMs?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply