CODEWITHSUNDEEP
sqlcommon-table-expression
Matt
Matt

Reputation: 510

Recursive CTE grab Parent from total iteration

I have a recursive CTE I made to print out a Bill of Materials. This CTE works fine for that purpose but what I'd like to add is another column for all the items to show the top level parent.

Here are my current results:

 ITEM               COMPONENT       LEVEL
 2326123679         216501          1
 2326123679         80015008        1
 216501             10006           2
 216501             13004           2
 216501             21010           2

What I'd like to output from the CTE is:

 PARENT             ITEM            COMPONENT       LEVEL
 2326123679         2326123679      216501          1
 2326123679         2326123679      80015008        1
 2326123679         216501          10006           2
 2326123679         216501          13004           2
 2326123679         216501          21010           2

Is this a possibility using a recursive CTE? Essentially I'd like to output:

 MAX(ITEM)

On each complete iteration.

CTE:

 WITH ReturnBOM ([Item], [Item Name], [Component],  Level)
 AS
 (
 SELECT  
        FinishedLevel.PMPRNO AS [Item],
        MasterItem.MMITDS AS [Item Name],
        FinishedLevel.PMMTNO  AS [Component],   
        1 AS Level
FROM    M3FDBPRD.MVXJDTA.MPDMAT  FinishedLevel
JOIN    M3FDBPRD.MVXJDTA.MITMAS  MasterItem
ON      FinishedLevel.PMPRNO = MasterItem.MMITNO
WHERE   MasterItem.MMITTY = 'Z10'
UNION ALL
    SELECT

        FinishedLevel.PMPRNO AS [Item],
        MasterItem.MMITDS AS [Item Name],
        FinishedLevel.PMMTNO  AS [Component],   
        Level + 1
FROM    M3FDBPRD.MVXJDTA.MPDMAT  FinishedLevel
INNER JOIN ReturnBOM  ItemRecursion
ON      ItemRecursion.[Component] = FinishedLevel.PMPRNO
JOIN    M3FDBPRD.MVXJDTA.MITMAS  MasterItem
ON      FinishedLevel.PMPRNO = MasterItem.MMITNO

 )
 SELECT 
 ReturnBOM.Item,
 ReturnBOM.[Item Name],
 ReturnBOM.Component,
 ReturnBOM.Level
 FROM ReturnBOM

Upvotes: 2

Views: 194

Answers (1)

Lamak
Lamak

Reputation: 70648

Without having data to test this, I think that this should be pretty straightforward:

 WITH ReturnBOM (Parent, [Item], [Item Name], [Component],  Level)
 AS
 (
 SELECT  
        FinishedLevel.PMPRNO AS [Parent],
        FinishedLevel.PMPRNO AS [Item],
        MasterItem.MMITDS AS [Item Name],
        FinishedLevel.PMMTNO  AS [Component],   
        1 AS Level
FROM    M3FDBPRD.MVXJDTA.MPDMAT  FinishedLevel
JOIN    M3FDBPRD.MVXJDTA.MITMAS  MasterItem
ON      FinishedLevel.PMPRNO = MasterItem.MMITNO
WHERE   MasterItem.MMITTY = 'Z10'
UNION ALL
    SELECT
        ItemRecursion.Parent,
        FinishedLevel.PMPRNO AS [Item],
        MasterItem.MMITDS AS [Item Name],
        FinishedLevel.PMMTNO  AS [Component],   
        Level + 1
FROM    M3FDBPRD.MVXJDTA.MPDMAT  FinishedLevel
INNER JOIN ReturnBOM  ItemRecursion
ON      ItemRecursion.[Component] = FinishedLevel.PMPRNO
JOIN    M3FDBPRD.MVXJDTA.MITMAS  MasterItem
ON      FinishedLevel.PMPRNO = MasterItem.MMITNO

 )
 SELECT 
 ReturnBOM.Parent,
 ReturnBOM.Item,
 ReturnBOM.[Item Name],
 ReturnBOM.Component,
 ReturnBOM.Level
 FROM ReturnBOM;

Upvotes: 2

Related Questions