Recursive Query for Bill of Materials

Recursive Query for Bill Of Materials

I cannot thank you enough for all your help, Gordon. You helped me with some of the fundamental flaws and I finished some tweaks and for the most part have what I need.... at least for now. This has been driving me nuts for quite some time. The code I settled on is below in case it could be of help to someone else down the road

  With BMStudy as (
  select bm.ppn_i, bm.cpn_i, bm.bomname_i, bm.QUANTITY_I, 1 as BOMLevel
  from BM010115 bm
  where bm.PPN_I like '0123105-HWT' and bm.BOMNAME_I like ''
  UNION ALL
  select ba.ppn_i, bb.cpn_i, bb.bomname_i, bb.quantity_i, 
  (BOMLevel + 1) as BOMLevel
  from BMStudy ba join
       BM010115 bb
       on ba.cpn_i = bb.ppn_i
  where bb.BOMNAME_I like ''
 )

 select top 1000 BMStudy.*, i.ITEMDESC from BMStudy, iv00101 i
 where CPN_I = itemnmbr
 order by BOMLEVEL, CPN_I ASC
 OPTION (MAXRECURSION 0)

Thanks again!

Recursive CTE Bill of Materials

I may be missing something, but it appears your JOIN criteria precludes recursion:

INNER JOIN BOMCost AS bc
ON bqd.TRX_ID = bc.Assembly

ASM0002909 doesn't equal ASM0002914, so no results from the bottom portion.

Update:

WITH BOMCost (Assembly, Component, PriceFromPO, Qty, BOMLevel)
AS
(
-- Anchor member definition
    SELECT asl.TRX_ID, asl.ITEMNMBR, asl.UNITCOST, asl.SERLTQTY,
    0 AS BOMLevel
    FROM DBO.BM10400 AS asl
    WHERE asl.TRX_ID = 'ASM0002909'
    UNION  ALL
-- Recursive member definition
    SELECT asl.TRX_ID, asl.ITEMNMBR, asl.UNITCOST, asl.SERLTQTY,
        BOMLevel + 1
    FROM DBO.BM10400 AS asl
    INNER JOIN DBO.BM10200_AssemblyQtyDetail AS bqd 
        ON asl.TRX_ID = bqd.TRX_ID
    INNER JOIN BOMCost AS bc
        ON bqd.TRANNUM = bc.Assembly

)

-- Statement that executes the CTE
SELECT DISTINCT Assembly, Component, PriceFromPO, Qty, BOMLevel
FROM BOMCost

Demo: SQL Fiddle

How can i fetch recursive data from Bill of materials tables

Use a recursive CTE. For example:

WITH BomTree (ID, Level, [Item Code], [Item Description], Depth)
AS
(
-- Anchor member definition
    SELECT m.*, 0 AS Depth
    FROM dbo.Master AS m
    WHERE m.[ID] = ?
    UNION ALL
-- Recursive member definition
    SELECT m.*, t.Depth + 1 AS Depth
    FROM dbo.Master AS m
    INNER JOIN dbo.BOM AS b
        ON m.[ID] = b.[Child Item ID]
    INNER JOIN BomTree AS t
        ON b.[Parent Item ID] = t.ID
)
-- Statement that executes the CTE
SELECT * FROM BomTree; 

Sage Manufacturing Line 50 Bill Of Materials, SQL Recursive Two Table Tree iteration

WITH BOM_CTE (HID, HRef, HDesc, SC, CDesc, CHID, CQ, SEQ)
AS
(
    --Anchor Member Definition
    SELECT H.ID as HID, H.BomReference as HRef, H.Description as HDesc, C.StockCode as SC,C.Description as CDesc,C.HeaderID as CHID,C.Quantity as CQ,1 as SEQ
    --GNT\SAGEL50.[sagel50_35648] refers to a database on a linked server on my main SQL server
    FROM [GNT\SAGEL50].[sagel50_35648].[dbo].BomHeaders H 
    JOIN [GNT\SAGEL50].[sagel50_35648].[dbo].BomComponents C
        ON H.ID = C.HeaderID
    WHERE H.BomReference like 'SA000009%'
    UNION ALL
    --Recursive Member Definition
    SELECT H.ID , H.BomReference f, H.Description , C.StockCode ,C.Description ,C.HeaderID ,C.Quantity, BC.SEQ+1
    FROM [GNT\SAGEL50].[sagel50_35648].[dbo].BomHeaders H 
    JOIN [GNT\SAGEL50].[sagel50_35648].[dbo].BomComponents C
        ON H.ID = C.HeaderID
    JOIN BOM_CTE BC
        ON H.BomReference = BC.SC
)
Select * From BOM_CTE 

This Works Now, after I removed the aliases, all I need to do now is pass the BOMReference I want to iterate through instead of the hardcoded 'SA000009'%

SELECT Query for a Bill of Materials

Thank you for those who suggested CTE's. That was exactly what I was looking for.

Here is the query I ended up with after some trial and error.

USE PartDatabase

GO

DECLARE @TheSinglePart CHAR(30) = '100001';

WITH BOM (PARPRT_02, COMPRT_02, QTYPER_02)

AS

(

-- Anchor member definition

SELECT e.PARPRT_02, e.COMPRT_02, e.QTYPER_02

FROM dbo.Product_Structure AS e

WHERE e.PARPRT_02 = @TheSinglePart

UNION ALL

-- Recursive member definition

SELECT e.PARPRT_02, e.COMPRT_02, e.QTYPER_02

FROM dbo.Product_Structure AS e

INNER JOIN BOM AS d

ON e.PARPRT_02 = d.COMPRT_02

)

SELECT *

FROM BOM;

Get recursive material costs (Bill of Materials)

I am not familiar in pseudo-code and I am not sure what programming language you are familiar with. Hence I try to answer it in code form.

Recursive method : CollectRawProduct(Product)

CollectRawProduct(Product product) {
    if (product has subProducts) {
        for ( subProduct : subProducts ) { // iterates the sub products
            CollectRawProduct( subProduct ); // visit subproduct
        }
    } else {
        rawProductList.add(product) // add to a raw product list
    }
}

The raw product list will contain the raw product like e.g for your case 4 legs one plate.

SQL server Query for Bill of materials quantity

You can use a recursive Common Table Expression to walk the hierarchy of parts, passing the quantity of the parent and multiplying the children's quantities by it.

DECLARE @bom int = 301755

CREATE TABLE #t(
    BOM int,
    KitID varchar(20),
    SubAssy varchar(20),
    BOMLevel int,
    StdQty float
)

INSERT #t(BOM, KitID, SubAssy, BOMLevel, StdQty) VALUES 
        (301755, '301755',   '31161201', 0, 1),
        (301755, '301755',   '29975413', 0, 2),
        (301755, '301755',   '299756', 0, 2),
        (301755, '301755',   '305958', 0, 1),
        (301755, '305958',   '311620', 1, 4),
        (301755, '305958',   '311620', 1, .1),
        (301755, '299756',   'RDBSSL012', 1, .1),
        (301755, '299756',   'RDBSSL012', 1, 3.417),
        (301755, '29975413', 'PLTSSL902', 1, 1),
        (301755, '29975413', 'CAPSSL4SCH40', 1, 1),
        (301755, '29975413', 'PIPSSL4SCH40', 1, 3.96),
        (301755, '29975413', 'LABSTR', 1, .166),
        (301755, '31161201', 'PIPSSL2SCH40', 1, 4)

;WITH cte AS (
    SELECT KitID, SubAssy, StdQty FROM #t WHERE KitID = @bom
UNION ALL
    SELECT #t.KitID, #t.SubAssy, cte.StdQty * #t.StdQty FROM #t 
    INNER JOIN cte ON cte.SubAssy = #t.KitID
)

SELECT * FROM cte ORDER BY KitID, SubAssy

---

Related Topics