Dynamics Community Forum Thread Details

SQL Recursive CTE 'Where-used'/BOM Explosion need SQL Expert!

https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

Hi all,

We have a data warehouse running off of AX 2012 R2. I'm trying to replicate the 'where-used' functionality using a recursive CTE in SQL Pivoted down to 7 levels, however I can't quite get my head round it and have been unsuccessful so far. Any help would be much appreciated asap. I'll post up what i've got so far.

Simply, I just want to get an item, get all it's components, then get where those components are used etc. and 'pivot' by levels up to 7. Thanks!

if someone could help me with this please...

USE tempdb;

IF OBJECT_ID('tempdb..#BOM') IS NOT NULL
    DROP TABLE #BOM;

CREATE TABLE #BOM
    (
      ID INT ,
      ITEMID NVARCHAR(50) ,
      BOMID NVARCHAR(50) ,
      LINENUM NUMERIC ,
      BOMQTY NUMERIC
    );

IF OBJECT_ID('tempdb..#BOMVERSION') IS NOT NULL
    DROP TABLE #BOMVERSION;

CREATE TABLE #BOMVERSION
    (
      ID INT ,
      ITEMID NVARCHAR(50) ,
      BOMID NVARCHAR(50) ,
      NAME NVARCHAR(50)
    );

WITH    BOM1
          AS ( SELECT   B.ITEMID AS BITEMID ,
                        BV.ITEMID AS BVITEMID ,
                        B.BOMID
               FROM     #BOM AS B
                        JOIN #BOMVERSION AS BV ON BV.BOMID = B.BOMID
             ),
        EXPLODE
          AS ( SELECT   B.BITEMID ,
                        B.BVITEMID ,
                        B.BOMID ,
                        0 AS [Level]
               FROM     BOM1 AS B
               UNION ALL
               SELECT   B.BITEMID ,
                        E.BVITEMID ,
                        E.BOMID ,
                        [E].[Level] + 1
               FROM     EXPLODE AS E
                        JOIN BOM1 AS B ON B.BOMID = E.BOMID
             )
    SELECT  EXPLODE.BITEMID ,
            EXPLODE.BVITEMID ,
            EXPLODE.BOMID ,
            EXPLODE.Level
    FROM    EXPLODE PIVOT ( MAX(BVITEMID) FOR Level IN ( [0], [1], [2], [3],
                                                         [4], [5], [6], [7] ) ) AS PVTBOM
OPTION  ( MAXRECURSION 7 );

4657.Untitled2.png