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/

Advertisements

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

  1. japhethnolt says:

    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.

  2. Mariano Gomez says:

    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

  3. Beat BUCHER says:

    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

  4. Beat,I am not familiar with the Manufacturing BOM tables, but I am sure the concept is the same.MG.-Mariano Gomez, MVP

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: