Exploding BOM through recursive CTE

Question

I'm trying to explode the BOM through recursive CTE, but I'm still getting the anchor rows only. However, I'm trying to use this SQL code:

With BOMTree_CTE

AS

(

--Anchor

Select 

B.BOMID

,B.ITEMID 

,B.LINENUM

,B.position

,B.BOMQTY

,B.INVENTDIMID

,0 as 'CurrentLevel'

,BV.BOMID as BVBomId

,BV.ITEMID 

FROM BOM B Left Join BomVersion BV On B.ItemID = BV.ItemId



Union All

Select 

BB.BOMID

,BB.ITEMID

,BB.LINENUM

,BB.position

,BB.BOMQTY

,BB.INVENTDIMID

,CurrentLevel+1

,CAST('1' as nvarchar(20)) 

,CAST('2' as nvarchar(20))

From Bom BB  Join BOMTree_CTE C On BB.BOMID = C.BVBomId

)

select * from BOMTree_CTE C

I don't wanna do it through X++, I need it through SQL!

Any help is highly appreciated!

score 0 · Answer 1 · answered Nov 19 '13 at 07:30

0

You are using Common Table Expressoins.

Well one thing you are missing is using DataAreaId and PartitionId (if using AX 2012 R2).
Take a look on the defined indexes.

answered Nov 19 '13 at 07:30

Jan B. Kjeldsen

17,817
5
32
50

I'm trying to do exactly this... however I am unable to find a complete example. – jhowe May 14 '15 at 09:10

score 0 · Answer 2 · answered Nov 20 '13 at 17:40

0

Well, apparently I found it, just used a stored procedure and passed the BOMId Parameter to the where statement of the Anchor CTE.

answered Nov 20 '13 at 17:40

Louie B

31
4

Exploding BOM through recursive CTE

2 Answers2