January 28, 2009 at 10:02 pm
I'm using SQL 2005. I've created a CTE recursive query to build a Bill Of Material file. I can get it to display the BOM correct, but I need to carry the "Program" name from the Parent Part Number(LEVEL 0) and display it with each record in the B.O.M.
How to you hold that Parent "Program" name and attach it with each Child record in the BOM?
Here's my sql statement:
----------------------------------------------------------------------------
WITH Hierarchy(Program, MFGPartNo, CompNo, CompDesc1, pt_part_type, LEVEL, MFGDesc1, TopLevel) AS
(
SELECT Program, MFGPartNo, CompNo, CompDesc1, pt_part_type, 0, MFGDesc1, TopLevel
FROM dbo.vw_ABE_FG_Parent1
WHERE MFGPartNo = '51-A3515-47000'
UNION ALL
SELECT dt.Program, dt.MFGPartNo, dt.CompNo, dt.CompDesc1, dt.pt_part_type, LEVEL+1, dt.MFGDesc1, dt.TopLevel
FROM dbo.vw_ABE_FG_Parent1 dt INNER JOIN Hierarchy h ON dt.MFGPartNo = h.CompNo
)
SELECT CompNo, CompDesc1, Program, MFGPartNo, MFGDesc1, pt_part_type, LEVEL, TopLevel
FROM Hierarchy
ORDER BY Level,CompNo
-----------------------------------------------------------------------------------
Here's example data from query:-----------------------------------------------------
(The column with "PS24 Rear Hat" is the Program column. I want to display "PS24 Rear Hat" for each record in that BOM.)
90-R6200-0700Bleeder CapPS24 Rear Hat51-A3515-47000PS24 Rr Hat Assy RHFIN GOOD0
90-R7201-0800Pin BootPS24 Rear Hat51-A3515-47000PS24 Rr Hat Assy RHFIN GOOD0
90-R7301-0800BushingPS24 Rear Hat51-A3515-47000PS24 Rr Hat Assy RHFIN GOOD0
51-A3515-67110Body CastingGeneral / Service51-A3515-47120Machined BodyWIP1
51-A3515-68080Support CastingGeneral / Service51-A3515-68500Machined SupportWIP1
80-A3506-66130Piston ForgingGeneral / Service80-A3506-67130Finished PistonWIP1
Thanks in advance for your help!
January 30, 2009 at 7:34 am
It would appear to be fairly easy, in that you would LEFT OUTER JOIN the following to your results:
SELECT Program
FROM dbo.vw_ABE_FG_Parent1
WHERE MFGPartNo = '51-A3515-47000'
Perhaps as follows:
;WITH PARENT_PROGRAM AS (
SELECT Program
FROM dbo.vw_ABE_FG_Parent1
WHERE MFGPartNo = '51-A3515-47000'
),
Hierarchy(Program, MFGPartNo, CompNo, CompDesc1, pt_part_type, LEVEL, MFGDesc1, TopLevel) AS (
SELECT Program, MFGPartNo, CompNo, CompDesc1, pt_part_type, 0, MFGDesc1, TopLevel
FROM dbo.vw_ABE_FG_Parent1
WHERE MFGPartNo = '51-A3515-47000'
UNION ALL
SELECT dt.Program, dt.MFGPartNo, dt.CompNo, dt.CompDesc1, dt.pt_part_type, LEVEL+1, dt.MFGDesc1, dt.TopLevel
FROM dbo.vw_ABE_FG_Parent1 dt INNER JOIN Hierarchy h ON dt.MFGPartNo = h.CompNo
)
SELECT P.Program, CompNo, CompDesc1, H.Program, MFGPartNo, MFGDesc1, pt_part_type, LEVEL, TopLevel
FROM Hierarchy AS H, PARENT_PROGRAM AS P
ORDER BY Level,CompNo
Of course, this does rely on their being only one record that has that particular MFGPartNo. Does that help?
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 10, 2009 at 12:05 pm
Thanks very much Steve, that worked! 😀
June 11, 2009 at 6:10 am
Thanks for the feedback!
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply