0

I have built a recursive CTE to blow out my bill of materials but many items have "phantom" parts in the bill of materials. I need to replace the "phantom" items with their bill of materials. Phantom items arent actual items but are a way we group a set of items. Some phantom items are also built with other phantom items so it does need to be recursive.

I have tried to simply replace the phantom items with a join to the table itself but run into problems when there are phantom items that are made up of other phantom items

SELECT
    c.TopLevelItem,
    c.ItemPath,
    c.ParentItem,
    ComponentItem = x.ComponentItem,
    ComponentQuantity = x.ComponentQuantity,
    c.ComponentItemLevel
FROM KOG_Datamart.dim.CurrentProductStructure c
    LEFT JOIN
        (
            SELECT
                ParentItem,
                ComponentItem,
                ComponentQuantity
            FROM KOG_Datamart.dim.CurrentProductStructure
                JOIN dim.Items i
                    ON TopLevelItem = i.ItemNumber
            WHERE
                i.ItemClass = 'phantom'
                AND ParentItem <> ComponentItem
            GROUP BY
                ParentItem,
                ComponentItem,
                ComponentQuantity
        ) x
        ON x.ParentItem = c.ParentItem
WHERE
    x.ParentItem IS NOT NULL
    AND c.ParentItem <> c.ComponentItem

Since the above code will duplicate items with multiple phantoms, I believe I will need to use a recursive cte to replace all the phantoms but I am fairly new to recursion.

I have listed a link below of the current state, example list of phantom items, and future state in a google doc, any help would be appreciated!

https://docs.google.com/spreadsheets/d/1ju0p1TWJcTksl4wL-btRfHmwQ5TV6a_m7wencFFAngw/edit?usp=sharing

Daniel
  • 37
  • 4
  • FWIW, I couldn't quickly figure out the structure of your data (I didn't inspect the SQL code though as I hoped to understand the problem before diving in). An example input data and the corresponding expected output would probably help me understand the problem. – Nickolay May 14 '19 at 23:38
  • https://docs.google.com/spreadsheets/d/1ju0p1TWJcTksl4wL-btRfHmwQ5TV6a_m7wencFFAngw/edit?usp=sharing – Daniel May 15 '19 at 01:41
  • I am using sql server 2017 – Daniel May 15 '19 at 01:42
  • item path is a nice to have but not necessary – Daniel May 15 '19 at 02:17

0 Answers0