Recursive query for a Bill of Materials table

  • Hi people,

    I could really do with a bit of help here. I have a table in the following format:

    StockCode1, StockCode2

    StockCode1, StockCode3

    StockCode1, StockCode4

    StockCode1, StockCode5

    StockCode2, StockCode10

    StockCode2, StockCode11

    StockCode2, StockCode12

    StockCode12, StockCode20

    Quick explination:

    StockCode1 is made up of StockCodes 2/3/4 & 5

    StockCode2 is made up of StockCodes 10/11 & 12

    StockCode12 is made up of StockCode 20

    OK, Now, I have a query that looks for certain types of sub assembly (S/A). It is as follows:

    SELECT [Field5]*[Enter Build Quantity] AS Expr1, BOM.Field1, BOM.Field2, BOM.Field3, BOM.Field4, BOM.Field5, BOM.Field6, Stock.stTempBinLocation, Stock.stUser1

    FROM BOM INNER JOIN Stock ON BOM.Field3=Stock.stStockCode

    WHERE (((BOM.Field1)=[Enter Part Number]) AND ((BOM.Field2)<>"D") AND ((BOM.Field6) Like "S/A*"));

    This query ONLY looks at the part number entered, it does not filter through all levels of the Bill of Materials and show me the results.

    Quick example (Again! Sorry, just trying to explain in FULL):

    If I was to query StockCode1 I get StockCode2 as a result, because it's a S/A, but I don't get StockCode12, because it's a sub assembly of a sub assembly. :w00t:

    Can anyone please help me with this, it's driving me insane :hehe:

    Any help would be most appreciated.

    Andy.

  • Andy, you might get some help if you posted table structure and sample data, including create and insert statements, and a sample of what you want the results to look like

    Greg

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • Parts or people, it's still a hierarchy... take a look at "Expanding Hierarchies" in Books Online... they've got a decent example. There's a couple of other ways to do it, as well but I've found that one to be one of the most stable. If it's a big list of parts and you do lots of lookups, you want want to try Celko's nested set model instead of the adjacency model you currently have.

    http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=1266295

    Do be advised that the code was not written specifically for T-SQL and I believe there's an error that misses the "right most node" that can be easily repaired, but it's well worth the read and the experiment.

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

  • DECLARE @BOM TABLE (Product varchar(11), Part varchar(11))

    INSERT INTO @BOM SELECT 'StockCode1', 'StockCode2'

    UNION ALL SELECT 'StockCode1', 'StockCode3'

    UNION ALL SELECT 'StockCode1', 'StockCode4'

    UNION ALL SELECT 'StockCode1', 'StockCode5'

    UNION ALL SELECT 'StockCode2', 'StockCode10'

    UNION ALL SELECT 'StockCode2', 'StockCode11'

    UNION ALL SELECT 'StockCode2', 'StockCode12'

    UNION ALL SELECT 'StockCode12', 'StockCode20'

    DECLARE @T1 TABLE (Product varchar(11), Part varchar(11))

    DECLARE @T2 TABLE (Product varchar(11), NewPart varchar(11), OldPart varchar(11))

    INSERT INTO @T1 SELECT Product, Part FROM @BOM

    INSERT INTO @T2 SELECT A.Product, B.Part, A.Part FROM @T1 A INNER JOIN @BOM B ON B.Product = A.Part

    WHILE (SELECT COUNT(*) FROM @T2) > 0

    BEGIN

    INSERT INTO @T1 SELECT Product, NewPart FROM @T2

    DELETE A FROM @T1 A WHERE EXISTS (SELECT * FROM @T2 Z WHERE Z.Product = A.Product AND Z.OldPart = A.Part)

    DELETE A FROM @T1 A WHERE A.Product IN (SELECT Z.OldPart FROM @T2 Z)

    DELETE FROM @T2

    INSERT INTO @T2 SELECT A.Product, B.Part, A.Part FROM @T1 A INNER JOIN @BOM B ON B.Product = A.Part

    END

    SELECT Product, Part FROM @T1 ORDER BY Product, Part

  • Close! Not seeing StockCode 12 anywhere in the result, though...

    --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 Moden (10/31/2007)


    Close! Not seeing StockCode 12 anywhere in the result, though...

    SrockCode12 uses StockCode20.

    So it is not shown.

    I'm only displaying the lowest level item.

  • Greg Snidow (10/30/2007)


    Andy, you might get some help if you posted table structure and sample data, including create and insert statements, and a sample of what you want the results to look like

    Greg

    Point taken Greg.

    Here is an Excel Spreadsheet extract from the BOM Table.

    Fields:

    Field1 = Parent Stock Code

    Field2 = Line Type:

    P = Part

    M = Manufactured Item

    D = Description Only Item

    Field3 = Child Stock Code

    Field4 = Duplicate of Field3 (just the way it extracts from the system)

    Field5 = BOM Quantity

    Field6 = Description

    The primary objective is to simply display "S/A of PCB" items to the user so that they can plan PCB manufacture in advance. So, using the attached BOM.xls file, let's say the user wants to find out ALL S/A of PCB items that are used on a TX5922.AA Stock Item.

    I need an input for the user that allows them to enter the required stock item and a build quantity. Then all I need is for the results to be displayed on the screen so that it can be printed ready for PCB manufacture.

    So, as per my previous post, I would enter the Stock Code TX5922.AA and I would get the following results:

    P5430.06 S/A of PCB (which is in this level of the BOM)

    P5431.06 S/A of PCB (which is in this level of the BOM)

    However, there is another S/A of PCB within this build but it is in a different S/A at a lower level. The result should be:

    P5430.06 S/A of PCB (which is in this level of the BOM)

    P5431.06 S/A of PCB (which is in this level of the BOM)

    P5431.94 S/A of PCB (which is in a sub-level of the BOM - being P5431.09.01)

    I am stuggling to get the correct results. Some of the BOMs have many more levels than this example, this is just a simple BOM.

    Thanks in advance for any help.

    Andy.:)

  • I don't know what is wrong, but I can neither open or save the attachement.

    However, my recommendation was to look at "Expanding Hierarchies" in Books Online... that recommendation still stands... it will solve your problem but you'll need to do a little on your own (like changing a table or column name here and there). 😉

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

  • Ok, i'm a little confused. Your query,...

    andy.dawson (10/24/2007)


    SELECT [Field5]*[Enter Build Quantity] AS Expr1, BOM.Field1, BOM.Field2, BOM.Field3, BOM.Field4, BOM.Field5, BOM.Field6, Stock.stTempBinLocation, Stock.stUser1

    FROM BOM INNER JOIN Stock ON BOM.Field3=Stock.stStockCode

    WHERE (((BOM.Field1)=[Enter Part Number]) AND ((BOM.Field2)<>"D") AND ((BOM.Field6) Like "S/A*"))

    wants to look at lines of the BOM where field6 is LIKE "S/A" and field 2 <> "D" which would mean you want to see all records for each parent stock code where field 2 = "P" or "M" *AND* field 6 LIKE "S/A". So, you have indicated you want to see the below lines for the parent stock code 'TX5922.AA'

    P = Part

    M = Manufactured Item

    D = Description Only Item

    ....

    So, as per my previous post, I would enter the Stock Code TX5922.AA and I would get the following results:

    P5430.06 S/A of PCB (which is in this level of the BOM)

    P5431.06 S/A of PCB (which is in this level of the BOM)

    However, there is another S/A of PCB within this build but it is in a different S/A at a lower level. The result should be:

    P5430.06 S/A of PCB (which is in this level of the BOM)

    P5431.06 S/A of PCB (which is in this level of the BOM)

    P5431.94 S/A of PCB (which is in a sub-level of the BOM - being P5431.09.01)

    My question is why would you also not want to see line 2 of your attachment, where parent code is TX5922.AA, field 2 = 'M' and field 6 is LIKE 'S/A%'? And line 5, the orange line also fits the criteria. I understand that you are taking that line into account by wanting to call line 41, but by your criteria line 5 should be returned as well. Do you only want to see records where field6 LIKE 'S/A OF PCB%'? Forgive me for maybe seeming dense, but I am trying to understand what you want.

    Greg

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • Andy. I was messing around with your spreadsheet today and this is what I came up with in my attempt to understand what the BOL procedure is doing. I would not use this if I were you, but it might help you see what you are asking of your tables.

    --THIS IS NOT THE BEST THING TO DO! but I think it will work. Somewhat.

    --You probably should take Jeff's suggestion about looking at BOL

    --Create a Temp table to hold your heirarchy. Probably could be made into procedure.

    IF OBJECT_ID('TempDB..#BOM','u') IS NOT NULL

    DROP TABLE #BOM

    GO

    CREATE TABLE #BOM

    (

    ID INT IDENTITY(1,1),

    Field1 VARCHAR(100),

    Field2 VARCHAR(100),

    FIeld3 VARCHAR(100),

    Field4 VARCHAR(100),

    Field5 VARCHAR(100),

    Field6 VARCHAR(100),

    BOM_Level INT,

    PRIMARY KEY (ID)

    )

    --Insert the Parent Codes that are never children and set the BOM_Level to 1

    INSERT INTO #BOM (Field1,Field2,Field3,Field4,Field5,Field6,BOM_Level)

    SELECT

    Field1,Field2,Field3,Field4,Field5,Field6,1

    FROM BOM

    WHERE Field2 IN ('P','M')

    AND SUBSTRING(Field6,1,3) = 'S/A'

    AND Field1 NOT IN (SELECT DISTINCT Field3 FROM BOM)

    --Insert the Parent Codes that are also children, and set the BOM_Level = 2

    INSERT INTO #BOM (Field1,Field2,Field3,Field4,Field5,Field6,BOM_Level)

    SELECT

    b1.Field1,b1.Field2,b1.Field3,b1.Field4,b1.Field5,b1.Field6,2

    FROM BOM b1,

    #BOM b2

    WHERE b1.Field1 = b2.Field3

    AND b1.Field3 NOT IN (SELECT DISTINCT Field1 FROM #BOM WHERE BOM_Level = 1)

    AND SUBSTRING(b1.Field6,1,3) = 'S/A'

    AND b1.Field2 IN ('P','M')

    --Level 3

    INSERT INTO #BOM (Field1,Field2,Field3,Field4,Field5,Field6,BOM_Level)

    SELECT

    b1.Field1,b1.Field2,b1.Field3,b1.Field4,b1.Field5,b1.Field6,3

    FROM BOM b1,

    #BOM b2

    WHERE b1.Field1 = b2.Field3

    AND b1.Field3 NOT IN (SELECT DISTINCT Field3 FROM #BOM WHERE BOM_Level IN (1,2))

    AND b1.Field1 NOT IN (SELECT DISTINCT Field1 FROM #BOM WHERE BOM_Level IN (1,2))

    AND SUBSTRING(b1.Field6,1,3) = 'S/A'

    AND b1.Field2 IN ('P','M')

    --Level 4

    INSERT INTO #BOM (Field1,Field2,Field3,Field4,Field5,Field6,BOM_Level)

    SELECT

    b1.Field1,b1.Field2,b1.Field3,b1.Field4,b1.Field5,b1.Field6,4

    FROM BOM b1,

    #BOM b2

    WHERE b1.Field1 = b2.Field3

    AND b1.Field3 NOT IN (SELECT DISTINCT Field3 FROM #BOM WHERE BOM_Level IN (1,2,3))

    AND b1.Field1 NOT IN (SELECT DISTINCT Field1 FROM #BOM WHERE BOM_Level IN (1,2,3))

    AND SUBSTRING(b1.Field6,1,3) = 'S/A'

    AND b1.Field2 IN ('P','M')

    --Level 5

    INSERT INTO #BOM (Field1,Field2,Field3,Field4,Field5,Field6,BOM_Level)

    SELECT

    b1.Field1,b1.Field2,b1.Field3,b1.Field4,b1.Field5,b1.Field6,5

    FROM BOM b1,

    #BOM b2

    WHERE b1.Field1 = b2.Field3

    AND b1.Field3 NOT IN (SELECT DISTINCT Field3 FROM #BOM WHERE BOM_Level IN (1,2,3,4))

    AND b1.Field1 NOT IN (SELECT DISTINCT Field1 FROM #BOM WHERE BOM_Level IN (1,2,3,4))

    AND SUBSTRING(b1.Field6,1,3) = 'S/A'

    AND b1.Field2 IN ('P','M')

    --Level 6

    INSERT INTO #BOM (Field1,Field2,Field3,Field4,Field5,Field6,BOM_Level)

    SELECT

    b1.Field1,b1.Field2,b1.Field3,b1.Field4,b1.Field5,b1.Field6,6

    FROM BOM b1,

    #BOM b2

    WHERE b1.Field1 = b2.Field3

    AND b1.Field3 NOT IN (SELECT DISTINCT Field3 FROM #BOM WHERE BOM_Level IN (1,2,3,4,5))

    AND b1.Field1 NOT IN (SELECT DISTINCT Field1 FROM #BOM WHERE BOM_Level IN (1,2,3,4,5))

    AND SUBSTRING(b1.Field6,1,3) = 'S/A'

    AND b1.Field2 IN ('P','M')

    --Level 7

    INSERT INTO #BOM (Field1,Field2,Field3,Field4,Field5,Field6,BOM_Level)

    SELECT

    b1.Field1,b1.Field2,b1.Field3,b1.Field4,b1.Field5,b1.Field6,7

    FROM BOM b1,

    #BOM b2

    WHERE b1.Field1 = b2.Field3

    AND b1.Field3 NOT IN (SELECT DISTINCT Field3 FROM #BOM WHERE BOM_Level IN (1,2,3,4,5,6))

    AND b1.Field1 NOT IN (SELECT DISTINCT Field1 FROM #BOM WHERE BOM_Level IN (1,2,3,4,5,6))

    AND SUBSTRING(b1.Field6,1,3) = 'S/A'

    AND b1.Field2 IN ('P','M')

    --Level 8

    INSERT INTO #BOM (Field1,Field2,Field3,Field4,Field5,Field6,BOM_Level)

    SELECT

    b1.Field1,b1.Field2,b1.Field3,b1.Field4,b1.Field5,b1.Field6,8

    FROM BOM b1,

    #BOM b2

    WHERE b1.Field1 = b2.Field3

    AND b1.Field3 NOT IN (SELECT DISTINCT Field3 FROM #BOM WHERE BOM_Level IN (1,2,3,4,5,6,7))

    AND b1.Field1 NOT IN (SELECT DISTINCT Field1 FROM #BOM WHERE BOM_Level IN (1,2,3,4,5,6,7))

    AND SUBSTRING(b1.Field6,1,3) = 'S/A'

    AND b1.Field2 IN ('P','M')

    --Level 9

    INSERT INTO #BOM (Field1,Field2,Field3,Field4,Field5,Field6,BOM_Level)

    SELECT

    b1.Field1,b1.Field2,b1.Field3,b1.Field4,b1.Field5,b1.Field6,9

    FROM BOM b1,

    #BOM b2

    WHERE b1.Field1 = b2.Field3

    AND b1.Field3 NOT IN (SELECT DISTINCT Field3 FROM #BOM WHERE BOM_Level IN (1,2,3,4,5,6,7,8))

    AND b1.Field1 NOT IN (SELECT DISTINCT Field1 FROM #BOM WHERE BOM_Level IN (1,2,3,4,5,6,7,8))

    AND SUBSTRING(b1.Field6,1,3) = 'S/A'

    AND b1.Field2 IN ('P','M')

    --Level 10

    INSERT INTO #BOM (Field1,Field2,Field3,Field4,Field5,Field6,BOM_Level)

    SELECT

    b1.Field1,b1.Field2,b1.Field3,b1.Field4,b1.Field5,b1.Field6,10

    FROM BOM b1,

    #BOM b2

    WHERE b1.Field1 = b2.Field3

    AND b1.Field3 NOT IN (SELECT DISTINCT Field3 FROM #BOM WHERE BOM_Level IN (1,2,3,4,5,6,7,8,9))

    AND b1.Field1 NOT IN (SELECT DISTINCT Field1 FROM #BOM WHERE BOM_Level IN (1,2,3,4,5,6,7,8,9))

    AND SUBSTRING(b1.Field6,1,3) = 'S/A'

    AND b1.Field2 IN ('P','M')

    --You can see that this is ridiculously redundant, and you would have to hard code

    --for every level of nesting as I did for 1 through 10. As I said, this probably

    --could be made into a procedure (like the one in BOL), but that is beyond my

    --ability right now.

    --The below will only work to return the values you specified in your post example .xls

    --Like the above you will have to hard code every level above 2, but since I only needed

    --2 for your example thats all I did.

    --Good luck, and I hope you find a better way.

    DECLARE @ParentCode VARCHAR(100)

    SELECT @ParentCode = 'TX5922.AA'

    SELECT *

    FROM #BOM

    WHERE BOM_Level = 1

    AND Field1 = @ParentCode

    AND Field2 IN ('M','P')

    AND SUBSTRING(Field6,1,10) = 'S/A OF PCB'

    UNION

    SELECT

    b1.*

    FROM #BOM b1,

    #BOM b2

    WHERE b2.Field3=b1.Field1

    AND b1.BOM_Level = 2

    AND SUBSTRING(b1.Field6,1,10) = 'S/A OF PCB'

    AND b1.Field1 NOT IN

    (

    SELECT

    Field1

    FROM #BOM

    WHERE BOM_Level = 1

    AND Field1 = @ParentCode

    AND Field2 IN ('M','P')

    AND SUBSTRING(Field6,1,3) = 'S/A'

    )

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • If it's a process THIS expensive, you should consider making this a permanent table. Meaning - keep the existing table just as it is, and have another to track the atomic parts manifest.

    I can't imagine that your sub assembly parts change all that often and the properly designed stored proc could "recalculate" only those items with affected assemblies/sub-assemblies.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Matt Miller (11/9/2007)


    If it's a process THIS expensive, you should consider making this a permanent table. Meaning - keep the existing table just as it is, and have another to track the atomic parts manifest.

    I can't imagine that your sub assembly parts change all that often and the properly designed stored proc could "recalculate" only those items with affected assemblies/sub-assemblies.

    I absolutely agree! And, if you're going to do that, make lookups fast! Joe Celko's "Nested Set" hierarchies are the berries!

    --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 Moden (11/9/2007)


    Matt Miller (11/9/2007)


    If it's a process THIS expensive, you should consider making this a permanent table. Meaning - keep the existing table just as it is, and have another to track the atomic parts manifest.

    I can't imagine that your sub assembly parts change all that often and the properly designed stored proc could "recalculate" only those items with affected assemblies/sub-assemblies.

    I absolutely agree! And, if you're going to do that, make lookups fast! Joe Celko's "Nested Set" hierarchies are the berries!

    Yep...I guess that's why they have things like that, so you don't do what I did.

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • My apologies Greg. I'd made my earlier example simpler and forgot to amend on my second post.

    I seem to have caused quite a fuss with this post! I dunno if am asking the impossible or just being incredibly bad at wording what I am trying to achieve!!!!?!?!??

    In the least confusing sentence possible:

    I want to display, on screen only, all parts with the description 'S/A of PCB'. Be it in the top level of the BOM or anywhere 'underneath' that level, given the example extract from the system I am using.

    In response to the comments regarding how often the Bill of Materials change, unfortunately, we operate on quite a regular bespoke set of BOMs. I extract the information each week to enable planning of PCB build. Headache I know, but that's the business. 🙁

  • You didn't listen to me... lookup "expanding hierarchies" in Books Online and adapt it. It WILL do as you ask.

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

Viewing 15 posts - 1 through 15 (of 45 total)

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