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/