asked on
SQL - Multi Level BOM
I need help with generating a query to pull from a table called "Mat_Req" to pull a multi-level BOM. When I type in the job# in my query, it would pull both purchased and fabricated parts. I am only interested in the sub component purchased materials to make these fabricated parts. So my main query, I had filtered down to only pulling fabricated items.
Here's the query I am using:
SELECT JOB_ID, PARENT_ID, TYPE, QTY, COMPONENT_ID, COMPONENT_TYPE, COMPONENT_QTY
FROM MAT_REQ
WHERE TYPE = 'FAB' AND JOB_ID = '12454'
The above query would pull the highlighted items in Green as per the image below. The item in green are all linked to job 12454, but the items not highlighted are different jobs to make that fabricated part. I need help to pull those items.
Raw Table Data. Noticed that line 7 and 8, Parent ID aaa10 require 2 materials to make that one. First one was a purchased item and second is a fabricated item. That fabricated item has a purchased raw material to make it so I also need to drill down. Some jobs can have as deep as 5 or so multi-levels.
![Multi-RawData.jpg]()
This is my final output. Only the purchased materials from the sub components when I put in the main Job#:
![Final Output]()
Multi-RawData.jpg
Multi-FinalData.jpg
Here's the query I am using:
SELECT JOB_ID, PARENT_ID, TYPE, QTY, COMPONENT_ID, COMPONENT_TYPE, COMPONENT_QTY
FROM MAT_REQ
WHERE TYPE = 'FAB' AND JOB_ID = '12454'
The above query would pull the highlighted items in Green as per the image below. The item in green are all linked to job 12454, but the items not highlighted are different jobs to make that fabricated part. I need help to pull those items.
Raw Table Data. Noticed that line 7 and 8, Parent ID aaa10 require 2 materials to make that one. First one was a purchased item and second is a fabricated item. That fabricated item has a purchased raw material to make it so I also need to drill down. Some jobs can have as deep as 5 or so multi-levels.
This is my final output. Only the purchased materials from the sub components when I put in the main Job#:
Multi-RawData.jpgMulti-FinalData.jpg
Microsoft SQL ServerMicrosoft ExcelSQL
Last Comment
holemania
Sounds like you're going to need a recursive cte (common table expression). It would be helpful if you would provide table creation and population scripts. This is not one that can be easily done with air code and is going to require some tinkering to get right.
ASKER
Here's the data in my table or fields that I need. With my basic query, I was able to get the 4 main components that are fabricated parts. I then need to find the sub assemblies which is the result of job 8822, 88271, and 9999 to get the sub components that are purchased materials to make those 4 main components.
WITH BOM(JOB_ID, PARENT_ID, TYPE, QTY, COMPONENT_ID, COMPONENT_TYPE, COMPONENT_QTY)
AS
(
SELECT '12454', 'A', 'Fab', 1, 'a1', 'Purc', 1
UNION
SELECT '12454', 'B', 'Fab', 1, 'c3', 'Fab', 1
UNION
SELECT '8822', 'c3', 'Fab', 1, 'a5', 'Purc', 2
UNION
SELECT '12454', 'D', 'Fab', 2, 'd2', 'Purc', 4
UNION
SELECT '12454', 'E', 'Fab', 1, 'aaa10', 'Fab', 2
UNION
SELECT '9999', 'aaa10', 'Fab', 2, 'bbb10', 'Purc', 2
UNION
SELECT '9999', 'aaa10', 'Fab', 2, 'ccc10', 'Fab', 2
UNION
SELECT '88271', 'ccc10', 'Fab', 2, 'ddd10', 'purc', 2
)
SELECT * FROM BOM
WITH BOM(JOB_ID, PARENT_ID, TYPE, QTY, COMPONENT_ID, COMPONENT_TYPE, COMPONENT_QTY)
AS
(
SELECT '12454', 'A', 'Fab', 1, 'a1', 'Purc', 1
UNION
SELECT '12454', 'B', 'Fab', 1, 'c3', 'Fab', 1
UNION
SELECT '8822', 'c3', 'Fab', 1, 'a5', 'Purc', 2
UNION
SELECT '12454', 'D', 'Fab', 2, 'd2', 'Purc', 4
UNION
SELECT '12454', 'E', 'Fab', 1, 'aaa10', 'Fab', 2
UNION
SELECT '9999', 'aaa10', 'Fab', 2, 'bbb10', 'Purc', 2
UNION
SELECT '9999', 'aaa10', 'Fab', 2, 'ccc10', 'Fab', 2
UNION
SELECT '88271', 'ccc10', 'Fab', 2, 'ddd10', 'purc', 2
)
SELECT * FROM BOM
ASKER
Just to clarify the above my query would only pull the 4 lines.
SELECT '12454', 'A', 'Fab', 1, 'a1', 'Purc', 1
UNION
SELECT '12454', 'B', 'Fab', 1, 'c3', 'Fab', 1
UNION
SELECT '12454', 'D', 'Fab', 2, 'd2', 'Purc', 4
UNION
SELECT '12454', 'E', 'Fab', 1, 'aaa10', 'Fab', 2
I then need to look at the MAT_REQ for any jobs that are considered sub assemblies/components to make the above 4 main parts. This is where I am stuck at and not sure how to proceed since I do not know how many levels deep it can go or what recursive I need to do to query the rest of the fab parts until it can get to the purchased parts and then list that.
So the other 4 lines if I was to manually search it, it's all in that same table just that it's a separate job to make that fabricated part use in the main job. These separate jobs can have fabricated parts as well and so on and so on.
SELECT JOB_ID, PARENT_ID, TYPE, QTY, COMPONENT_ID, COMPONENT_TYPE, COMPONENT_QTY
FROM MAT_REQ
WHERE TYPE = 'FAB' AND JOB_ID = '12454'
SELECT '12454', 'A', 'Fab', 1, 'a1', 'Purc', 1
UNION
SELECT '12454', 'B', 'Fab', 1, 'c3', 'Fab', 1
UNION
SELECT '12454', 'D', 'Fab', 2, 'd2', 'Purc', 4
UNION
SELECT '12454', 'E', 'Fab', 1, 'aaa10', 'Fab', 2
I then need to look at the MAT_REQ for any jobs that are considered sub assemblies/components to make the above 4 main parts. This is where I am stuck at and not sure how to proceed since I do not know how many levels deep it can go or what recursive I need to do to query the rest of the fab parts until it can get to the purchased parts and then list that.
So the other 4 lines if I was to manually search it, it's all in that same table just that it's a separate job to make that fabricated part use in the main job. These separate jobs can have fabricated parts as well and so on and so on.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks. I will take a look. Looks like it only goes 1 level deep. If it's more than 1 level, do I need to do another "Union all" and add the 2nd query and change the JOIN?
No, the common-table expression recursion has a maximum depth of 32767 which I'm sure is more than enough for your case.
holemania, do you still need help with this question?
ASKER
Sorry had been out and just got back in the office today. I ran the example Brian provided. Seems to be working somewhat. However, I would expect the result to also include in the last row with job ID 88271 as a level 2. However, this is not showing up.
My final result or output has ddd10. Basically what you had, and if I was to filter just the component_type to purc, it would give me most of my result, but then it doesn't seem to be going any more level deep?
Here's my result after tweaking the select statement to get just the purc items. It doesn't seem to go deeper.
My final result or output has ddd10. Basically what you had, and if I was to filter just the component_type to purc, it would give me most of my result, but then it doesn't seem to be going any more level deep?
Here's my result after tweaking the select statement to get just the purc items. It doesn't seem to go deeper.
DECLARE @JobID VARCHAR(20);
DECLARE @BOM TABLE
(
JOB_ID VARCHAR(20),
PARENT_ID VARCHAR(20),
[TYPE] VARCHAR(20),
QTY INT,
COMPONENT_ID VARCHAR(20),
COMPONENT_TYPE VARCHAR(20),
COMPONENT_QTY INT
);
INSERT INTO @BOM (JOB_ID, PARENT_ID, [TYPE], QTY, COMPONENT_ID, COMPONENT_TYPE, COMPONENT_QTY)
VALUES ('12454', 'A', 'Fab', 1, 'a1', 'Purc', 1),
('12454', 'B', 'Fab', 1, 'c3', 'Fab', 1),
('8822', 'c3', 'Fab', 1, 'a5', 'Purc', 2),
('12454', 'D', 'Fab', 2, 'd2', 'Purc', 4),
('12454', 'E', 'Fab', 1, 'aaa10', 'Fab', 2),
('9999', 'aaa10', 'Fab', 2, 'bbb10', 'Purc', 2),
('9999', 'aaa10', 'Fab', 2, 'ccc10', 'Fab', 2),
('88271', 'ccc10', 'Fab', 2, 'ddd10', 'purc', 2)
;
SELECT @JobID = '12454';
WITH cteBOM AS
(
SELECT BOM.JOB_ID, BOM.[TYPE], BOM.QTY, BOM.COMPONENT_ID, BOM.PARENT_ID,
BOM.COMPONENT_TYPE, BOM.COMPONENT_QTY, BOM.COMPONENT_ID AS ROOTCOMPONENT_ID,
0 AS [LEVEL]
FROM @BOM AS BOM
LEFT OUTER JOIN @BOM AS BOMParent
ON BOM.PARENT_ID = BOMParent.COMPONENT_ID
WHERE BOM.[TYPE] = 'FAB'
AND BOM.JOB_ID = @JobID
AND BOMParent.COMPONENT_ID IS NULL
UNION ALL
SELECT BOM.JOB_ID, BOM.[TYPE], BOM.QTY, BOM.COMPONENT_ID, BOM.PARENT_ID,
BOM.COMPONENT_TYPE, BOM.COMPONENT_QTY, cteBOM.ROOTCOMPONENT_ID,
cteBOM.[LEVEL] + 1 AS [LEVEL]
FROM cteBOM
INNER JOIN @BOM AS BOM
ON cteBOM.COMPONENT_ID = BOM.PARENT_ID
WHERE BOM.COMPONENT_TYPE = 'Purc'
)
SELECT COMPONENT_ID, COMPONENT_TYPE, COMPONENT_QTY, QTY*COMPONENT_QTY EXT_QTY
FROM cteBOM
WHERE COMPONENT_TYPE = 'PURC'
ORDER BY CASE COMPONENT_ID WHEN 'a1' THEN 0
WHEN 'a5' THEN 1
WHEN 'd2' THEN 2
WHEN 'bbb10' THEN 3
END
ASKER
Here's the result with the query.
![Query Result]()
ASKER
Modified the "WHERE" clause a bit and got it to work now. I just filtered "WHERE BOM.COMPONENT_TYPE = 'Purc'" from the 2nd query within the CTE code block and that fixed it. Thanks.