Explode Production BOM & Stamp Master Item in Results Set

Viewed 27

I need to build a query that returns the item master, plus the BOMs and sub BOMS. For example, the master BOM item is 'mealdeal'. There are three components in that - 'drink', 'chocolate bar' and 'sandwich'. 'Sandwich' is a BOM in itself, and may contain other BOMs depending on what is in the sandwich. I have written a query which is fine if I want to filter down to one item, as the lines it returns will just be the lines for that item, but I need to create a table to build other reports off. What I'm trying to achieve is a separate column which stamps / records the master item against all of the levels. This is what I have so far

WITH ExplodeBOM ([No.], [Production BOM No.], [Quantity], [Quantity per], [Unit of Measure Code], [BOM Level]) AS
(
    SELECT        [No.], [Production BOM No.], Quantity, [Quantity per], [Unit of Measure Code], CAST(1 AS VARCHAR(MAX)) AS [BOM Level]
    FROM            dbo.[Production BOM Line]
    UNION ALL
    SELECT        [BOML1].[No.], [BOML1].[Production BOM No.], [BOML1].[Quantity], [BOML1].[Quantity per], [BOML1].[Unit of Measure Code], CAST([BOM Level] + 1 AS VARCHAR(MAX)) AS [BOM Level]
    FROM            dbo.[Production BOM Line] AS BOML1
        INNER JOIN ExplodeBOM AS MasterBOM
        ON BOML1.[Production BOM No.] = MasterBOM.[No.]
)
SELECT        Item.[Production BOM No.], Description, Item.[No.],ExplodeBOM.[No.], ExplodeBOM.[Production BOM No.], [Quantity], [Quantity per], [Unit of Measure Code], [BOM Level]
FROM            Item
inner join ExplodeBOM on Item.[Production BOM No.] = [ExplodeBOM].[Production BOM No.]

I thought I could achieve it by adding in the item table, but that doesn't work. Ideally I would like a results set that looks something like this

MasterBOM Component
MEALDEAL1 COKE
MEALDEAL1 CHOCOLATE
MEALDEAL1 TURKEYSANDWICH
MEALDEAL1 BREAD
MEALDEAL1 MAYO
MEALDEAL1 CHEESE
MEALDEAL2 SPRITE
MEALDEAL2 DORITOS
MEALDEAL2 TUNASUB
MEALDEAL2 SUBROLL
MEALDEAL2 TUNA
MEALDEAL2 CUCUMBER

Where turkey sandwich and tuna sub are BOM components of mealdeal / mealdeal2, but they are BOMS themselves, which contain the ingredients to make them.

Do I need to change the recursive CTE somehow?

0 Answers
Related