Recursive CTE – Bill of Materials – BOM View
09-Apr-1010 Leave a comment
Difficult but got there ! And worth it !
/*
Based on a table Product.Products and Product.BillOfMaterials.
*/
/*
SELECT * FROM Product.Products WHERE ID = 738
SELECT * FROM Product.vBillOfMaterials WHERE ProductAssemblyID = 738 ORDER BY BOMLevel
SELECT * FROM Product.vBillOfMaterialsName WHERE ProductAssemblyID = 738 ORDER BY BOMLevel
*/
CREATE VIEW [Product].[vBillOfMaterials]
AS
WITH BOM_cte
AS
(
SELECT b.ProductID AS ProductAssemblyID, ID, ProductID, ComponentID, ItemNo, Quantity, LastUpdate, LastUpdateBy, AlternativeAccy, 0 AS BOMLevel
FROM Product.BillOfMaterials b
UNION ALL
SELECT cte.ProductAssemblyID, b.ID, b.ProductID, b.ComponentID, b.ItemNo, b.Quantity, b.LastUpdate, b.LastUpdateBy, b.AlternativeAccy, BOMLevel + 1 AS BOMLevel
FROM [BOM_cte] cte
INNER JOIN [Product].[BillOfMaterials] b
ON b.ProductID = cte.[ComponentID]
)
SELECT BOM.* FROM BOM_cte BOM