Link to home
Start Free TrialLog in
Avatar of holemania
holemania

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.
 User generated image
This is my final output.  Only the purchased materials from the sub components when I put in the main Job#:
User generated imageMulti-RawData.jpg
Multi-FinalData.jpg
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

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.
Avatar of holemania
holemania

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
Just to clarify the above my query would only pull the 4 lines.

SELECT JOB_ID, PARENT_ID, TYPE, QTY, COMPONENT_ID, COMPONENT_TYPE, COMPONENT_QTY
 FROM MAT_REQ
 WHERE TYPE = 'FAB' AND JOB_ID = '12454'

Open in new window


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
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
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.

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

Open in new window

Here's the result with the query.

User generated image
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.