Wednesday, April 22, 2009

Using T-SQL and recursive CTE to generate a BOM tree

Ever wonder how to replicate a Dynamics GP Bill of Materials tree with SQL Server? The following query uses recursive CTE to generate a BOM tree.

BOMTree.sql

-- Mariano Gomez, MVP
-- This query is provided "AS IS". No warranties expressed or implied.
WITH BOMCTE (bom_path, tree_path, item, component, qty, effective_date, lvl) AS (
SELECT CAST(RTRIM(ITEMNMBR) AS VARCHAR(MAX)),
CAST(RTRIM(ITEMNMBR) AS VARCHAR(MAX)),
ITEMNMBR,
NULL,
--CAST(NULL AS VARCHAR(MAX)),
CAST(NULL AS NUMERIC(19, 5)),
effective_date,
0 AS lvl
FROM BM00101

UNION ALL

SELECT CAST(RTRIM(H.ITEMNMBR) + '/' +
RTRIM(B.CMPTITNM) AS VARCHAR(MAX)),
CAST(REPLICATE(' ', 13) +
RTRIM(B.CMPTITNM) AS VARCHAR(MAX)),
H.ITEMNMBR,
B.CMPTITNM,
B.Design_Qty,
B.effective_date,
1 AS lvl
FROM BM00101 AS H
JOIN BM00111 AS B ON (H.ITEMNMBR = B.ITEMNMBR)

UNION ALL

SELECT C.bom_path + '/' + RTRIM(B.CMPTITNM),
CAST(REPLICATE(' ', (C.lvl + 1) * 13) + RTRIM(B.CMPTITNM) AS VARCHAR(MAX)),
B.ITEMNMBR,
B.CMPTITNM,
B.Design_Qty,
B.Effective_Date,
C.lvl + 1
FROM BM00111 AS B
JOIN BOMCTE AS C ON (B.CMPTITNM = C.item)
)
SELECT DISTINCT bom_path, tree_path, lvl
FROM BOMCTE
ORDER BY bom_path;

A common table expression (CTE) provides the significant advantage of being able to reference itself, thereby creating a recursive CTE. A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained.

A query is referred to as a recursive query when it references a recursive CTE. Returning hierarchical data is a common use of recursive queries, for example: Displaying employees in an organizational chart, or data in a bill of materials scenario in which a parent product has one or more components and those components may, in turn, have subcomponents or may be components of other parents.

The bottom line is, you can apply this concept to Dynamics GP's BOM header and detail tables in both the Inventory Control and Manufacturing modules to generate a path and tree representation of your bill.

Related Articles

Recursive Queries Using Common Table Expressions - MSDN. Click here.
Hierarchies in SQL Server 2008 - Plamen Ratchev. Click here.

Acknowledgements

Thanks to Microsoft SQL Server MVP Plamen Ratchev at SQL Studio for his contribution to this article. You can visit Plamen's blog at http://pratchev.blogspot.com/




Until next post!

MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

6 comments:

japhethnolt said...

Just trying out this script and it's great but I think I found a bug. I think the recursive join back to the CTE should be

JOIN BOMCTE AS C ON (B.itemnmbr = C.component)

Let me know what you think.

Mariano Gomez said...

Japheth,

As always much thanks for your input. I will test your suggestion and post back with the results.

Best regards,

MG.-
Mariano Gomez, MIS
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Beat BUCHER said...

Hi Mariano,
The recursive CTE stuff looks very promising to me, and I tried to apply it by analogy to the Manufacturing BOM Tree... but then I get a type mismatch error somehow : << Types don't match between the anchor and the recursive part in column "item" of recursive query "BOMCTE". >>
Any idea ? I can send you the code if you would like...
Have a great day,
Beat

Mariano Gomez said...

Beat,
I am not familiar with the Manufacturing BOM tables, but I am sure the concept is the same.

MG.-
Mariano Gomez, MVP

Kathy Johnson said...

Customer's are asking for this but the component costs and rollup cost to the parent item. Do you have anything that would add this data already written?

May said...

Is it possible to have the roll up qty for the components? Based on the above script, it will always pick the design qty defined for the sub-components based on 1 unit of the previous parent's item.

Any suggestion on how that can be done?