Recursive CTE – Bill of Materials – BOM View

 
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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: