Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Recursive query for a Bill of Materials table Expand / Collapse
Author
Message
Posted Wednesday, October 24, 2007 6:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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, 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.

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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 2:39 PM
Points: 1,564, Visits: 2,366
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.
Post #416804
Posted Tuesday, October 30, 2007 5:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:13 AM
Points: 35,996, Visits: 30,290
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #416838
Posted Tuesday, October 30, 2007 7:41 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld 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 @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
Post #416854
Posted Wednesday, October 31, 2007 1:47 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:13 AM
Points: 35,996, Visits: 30,290
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 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #416906
Posted Wednesday, October 31, 2007 2:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld 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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:13 AM
Points: 35,996, Visits: 30,290
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 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #420545
Posted Friday, November 09, 2007 9:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 2:39 PM
Points: 1,564, Visits: 2,366
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.
Post #420603
Posted Friday, November 09, 2007 2:08 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 2:39 PM
Points: 1,564, Visits: 2,366
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.
Post #420701
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse