Problem With Common Table Expression (CTE)

Amytdev

I constructed the following but I get an error results trying to run it. What am I doing wrong

Error:

Msg 530, Level 16, State 1, Line 1

The statement terminated. The maximum recursion 2 has been exhausted before statement completion.

WITH AllPNBom ([Parent Part Number], [Component Part Number], ItemId, ParentId, Part_Class, HLevel)

AS

(

-- Anchor member definition

SELECT [Parent Part Number],[Component Part Number], ItemId, ParentId, Part_Class,

0 AS HLevel

FROM AllBoms

WHERE [Parent Part Number]='305-0345-001'

UNION ALL

-- Recursive member definition

SELECT e.[Parent Part Number],e.[Component Part Number], e.ItemId, e.ParentId, e.Part_Class,

apb.HLevel + 1 as HLevel

FROM AllBoms e

INNER JOIN AllPNBom apb

ON e.ParentId = apb.ItemID

)

-- Statement that executes the CTE

SELECT * from AllPNBom order by HLevel, [Component Part Number]

Option (maxrecursion 2)

Louis Davidson

Maxrecursion cancels the statement and raises an error. If you are simply trying to limit the number of levels returned, use a WHERE clause:

...

SELECT * from AllPNBom
WHERE HLevel <= 2 --or something along these lines

order by HLevel, [Component Part Number]
--Option (maxrecursion 2)

You won't need the maxrecursion in this case, though you could set it to 3, just to make sure that it doesn't go out of control.

Amytdev

That worked, however, it took 10 minutes when I limited the levels down to 2. The problem is that I will not know how many levels deep so is there anything that I could do to speed up the result time My goal was to write something that will allow me to do an update query to one of the tables that this view represents. Any suggestions

Louis Davidson

This question will require more information, particularly because you are going to have to look at the plan. Of particular interest is how many rows are involved. See if a table scan is occuring where it could be indexed.

Why are you limiting it to two if it could be far deeper Can you provide a bit more guidance

Problem With Common Table Expression (CTE)

Re: Problem With Common Table Expression (CTE)

Re: Problem With Common Table Expression (CTE)

Re: Problem With Common Table Expression (CTE)