How to return lowest level parts from BOM

  • pietlinden

    SSC Guru

    Points: 62451

    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?

  • Phil Parkin

    SSC Guru

    Points: 243681

    Add hierarchy level to your CTE and base your selection on that?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • pietlinden

    SSC Guru

    Points: 62451

    That’s what I was thinking. Get max of level and return all parts having that same level.

    Makes sense! Thanks!

  • Phil Parkin

    SSC Guru

    Points: 243681

    As long as max level is the same for all of the lowest-level items, that is. Otherwise, it gets trickier.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Eddie Wuerch

    SSChampion

    Points: 12268

    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

  • jcelko212 32090

    SSCrazy Eights

    Points: 8844

    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. 

  • tony 8714

    Newbie

    Points: 2

    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.

  • pietlinden

    SSC Guru

    Points: 62451

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

     

  • Jeff Moden

    SSC Guru

    Points: 994647

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • pietlinden

    SSC Guru

    Points: 62451

    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

  • pietlinden

    SSC Guru

    Points: 62451

    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

  • Phil Parkin

    SSC Guru

    Points: 243681

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • pietlinden

    SSC Guru

    Points: 62451

    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?

    Attachments:
    You must be logged in to view attached files.
  • Phil Parkin

    SSC Guru

    Points: 243681

    Here's an in-line version of that image.

    BOM_exploded

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply