SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Recursive query for a Bill of Materials table


Recursive query for a Bill of Materials table

Author
Message
andy.dawson
andy.dawson
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 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. w00t

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

Any help would be most appreciated.


Andy.
Greg Snidow
Greg Snidow
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2173 Visits: 2486
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88096 Visits: 41128
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Koji Matsumura
Koji Matsumura
Old Hand
Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)

Group: General Forum Members
Points: 389 Visits: 1918
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88096 Visits: 41128
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Koji Matsumura
Koji Matsumura
Old Hand
Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)

Group: General Forum Members
Points: 389 Visits: 1918
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.
andy.dawson
andy.dawson
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 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.Smile
Attachments
BOM.xls (72 views, 32.00 KB)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88096 Visits: 41128
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). Wink

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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Greg Snidow
Greg Snidow
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2173 Visits: 2486
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.
Greg Snidow
Greg Snidow
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2173 Visits: 2486
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search