Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Recursive query for a Bill of Materials table Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, October 24, 2007 6:46 AM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, May 05, 2009 1:57 AM Points: 6, Visits: 20
 Hi people,I could really do with a bit of help here. I have a table in the following format:StockCode1, StockCode2StockCode1, StockCode3StockCode1, StockCode4StockCode1, StockCode5StockCode2, StockCode10StockCode2, StockCode11StockCode2, StockCode12StockCode12, StockCode20Quick explination:StockCode1 is made up of StockCodes 2/3/4 & 5StockCode2 is made up of StockCodes 10/11 & 12StockCode12 is made up of StockCode 20OK, 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.stUser1FROM BOM INNER JOIN Stock ON BOM.Field3=Stock.stStockCodeWHERE (((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. Can anyone please help me with this, it's driving me insane Any help would be most appreciated.Andy.
Post #414386
 Posted Tuesday, October 30, 2007 2:48 PM
 SSCommitted Group: General Forum Members Last Login: Friday, December 06, 2013 12:33 PM Points: 1,564, Visits: 2,356
 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 likeGreg Greg_________________________________________________________________________________________________The glass is at one half capacity: nothing more, nothing less.
Post #416804
 Posted Tuesday, October 30, 2007 5:48 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 4:27 PM Points: 34,580, Visits: 28,763
 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=1266295Do 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." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #416838
 Posted Tuesday, October 30, 2007 7:41 PM
 Old Hand Group: General Forum Members Last Login: Sunday, March 30, 2008 9:53 PM Points: 311, Visits: 1,918
 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 @BOMINSERT INTO @T2 SELECT A.Product, B.Part, A.Part FROM @T1 A INNER JOIN @BOM B ON B.Product = A.PartWHILE (SELECT COUNT(*) FROM @T2) > 0BEGININSERT INTO @T1 SELECT Product, NewPart FROM @T2DELETE 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 @T2INSERT INTO @T2 SELECT A.Product, B.Part, A.Part FROM @T1 A INNER JOIN @BOM B ON B.Product = A.PartENDSELECT Product, Part FROM @T1 ORDER BY Product, Part
Post #416854
 Posted Wednesday, October 31, 2007 1:47 AM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 4:27 PM Points: 34,580, Visits: 28,763
 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." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #416906
 Posted Wednesday, October 31, 2007 2:48 AM
 Old Hand Group: General Forum Members Last Login: Sunday, March 30, 2008 9:53 PM Points: 311, Visits: 1,918
 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.
Post #416916
 Posted Friday, November 09, 2007 2:39 AM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, May 05, 2009 1:57 AM Points: 6, Visits: 20
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.:)

Post Attachments
 BOM.xls (32 views, 32.50 KB)
Post #420416
 Posted Friday, November 09, 2007 8:05 AM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 4:27 PM Points: 34,580, Visits: 28,763
 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." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #420545
 Posted Friday, November 09, 2007 9:48 AM
 SSCommitted Group: General Forum Members Last Login: Friday, December 06, 2013 12:33 PM Points: 1,564, Visits: 2,356
 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.stUser1FROM BOM INNER JOIN Stock ON BOM.Field3=Stock.stStockCodeWHERE (((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 = PartM = Manufactured ItemD = 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.
Post #420603
 Posted Friday, November 09, 2007 2:08 PM
 SSCommitted Group: General Forum Members Last Login: Friday, December 06, 2013 12:33 PM Points: 1,564, Visits: 2,356
 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 #BOMGOCREATE 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 1INSERT 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 3INSERT 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 4INSERT 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 5INSERT 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 6INSERT 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 7INSERT 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 8INSERT 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 9INSERT 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 10INSERT 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'UNIONSELECT 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.
Post #420701

 Permissions