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.
-- 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
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)
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.
Recursive Queries Using Common Table Expressions – MSDN. Click here. Hierarchies in SQL Server 2008 – Plamen Ratchev. Click here.