October 24, 2007 at 6:46 am
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.
October 30, 2007 at 2:48 pm
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.
October 30, 2007 at 5:48 pm
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
Change is inevitable... Change for the better is not.
October 30, 2007 at 7:41 pm
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
October 31, 2007 at 1:47 am
Close! Not seeing StockCode 12 anywhere in the result, though...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 31, 2007 at 2:48 am
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.
November 9, 2007 at 2:39 am
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 likeGreg
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.:)
November 9, 2007 at 8:05 am
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
Change is inevitable... Change for the better is not.
November 9, 2007 at 9:48 am
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.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.
November 9, 2007 at 2:08 pm
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.
November 9, 2007 at 10:10 pm
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?
November 9, 2007 at 10:37 pm
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
Change is inevitable... Change for the better is not.
November 9, 2007 at 10:40 pm
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.
November 11, 2007 at 1:07 pm
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. 🙁
November 11, 2007 at 5:18 pm
You didn't listen to me... lookup "expanding hierarchies" in Books Online and adapt it. It WILL do as you ask.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 45 total)
You must be logged in to reply to this topic. Login to reply