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