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

Question

I am trying to recursively iterate through a Bill Of Materials(BOM) in Sage Manufacturing Line 50 on MS-SQL server.

The BOM tree structure is contained in two tables BOMHeader and BOMComponent. These are linked by BOMHeader.ID = BOMComponent.HeaderID. So by linking these and querying With a specific BomHeader.BOMReference, I get The first tier of the BOM.

The Recursion occurs because each BOMComponent can itself be an assembly who's StockCode, which if it exists as a BOMHeader.BomReference indicates that it is a sub BOM and has sub-components in the BOMComponents Table.

I Spent about half an hour drawing a diagram, but I cannot post it as I need 10 reputation points

WITH BOM_CTE (HID, HRef, HDesc, SC, CDesc, CHID, CQ, SEQ)
AS
(
    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
    FROM [GNT\SAGEL50].[sagel50_46387].[dbo].BomHeaders H 
    JOIN [GNT\SAGEL50].[sagel50_46387].[dbo].BomComponents C
        ON H.ID = C.HeaderID
    WHERE H.BomReference like 'SA000001%'
    UNION ALL
    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,BC.SEQ+1
    FROM [GNT\SAGEL50].[sagel50_46387].[dbo].BomHeaders H 
    JOIN [GNT\SAGEL50].[sagel50_46387].[dbo].BomComponents C
        ON H.ID = C.HeaderID
    JOIN BOM_CTE BC
        ON HRef = BC.SC
)
Select * From BOM_CTE

The above code only lists the top level, and will not re-curse, by seeing if a resultant stockcode derived from the BOMComponents Table appears in the BOMHeader Table as a BOMReference (meaning it has sub components).

The recursion should end when all BomComponents have no StockCode reference in the BomHeader.BomReference (meaning there is no lower level)

I am after a recursive SQL code statement that can achieve a two linked table recursive tree lookup. I have looked a a lot of BOM questions, but they seem to be in one table, and also two table CTE, but I can't get my head around it, as I'm fairly new to SQL, especially recursion.

Thanks in advance for any help

Adam

Could you give us a better table structure? Maybe using ascii and code; and then a query of what you're trying to do? Right now this just reads like a wall of text and it's hard to follow what you need. — George Stocker, Mar 07 '14 at 14:59
Does the recursion extend n-levels or is there a limit to how "tall" the tree may be? (You can hard-code the expansion if the limit is reasonable) - PS is **BOM** supposed to mean something? I keep parsing "Byte Order Mark". — , Mar 07 '14 at 21:15
BOM is short for Bill Of Materials, there is no specific depth, it depends on how people create the BOM's, but I would suspect that, in my case the depth is no more than 5 or 6 levels, but there is no limit that I know of. — Adamgsb, Mar 08 '14 at 07:34

score 0 · Answer 1 · edited May 23 '17 at 10:33

0

This thread has an interesting answer and points back to an older thread showing how to do "hard-coded" expansion.

mysql recursive(tree) parent child category

edited May 23 '17 at 10:33

Community

1
1

answered Mar 07 '14 at 21:34

Thanks for the reply, but it's not really what I was looking for, Plus it's only one table, I need two table, and I was hoping to do it with SQL Recursive CTE's. – Adamgsb Mar 08 '14 at 09:46

Adamgsb · Accepted Answer · 2014-03-08T21:28:05.800

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'%

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

2 Answers2