Help! Can I control the recursion times of a recursive CTE?

I have a table describing a hierarchy structure and the number of levels is very large, say 10000. Can I control the recursive CTE to get the first 1000 levels? Thanks!

Sorry, wrong format in last post. Here's the question:

I have a table describing a hierarchy structure and the number of levels is very large, say 10000. Can I control the recursive CTE to get the first 1000 levels? Thanks!

|||Check out maxrecursion here http://msdn2.microsoft.com/en-us/library/ms175972.aspx|||Thanks. I tried it but got some error:

CREATE TABLE Table1 ( fr int, t int)
INSERT Table1 VALUES (1, 2)
INSERT Table1 VALUES (2, 3)
INSERT Table1 VALUES (3, 4)
INSERT Table1 VALUES (1, 3)
INSERT Table1 VALUES (1, 4)
INSERT Table1 VALUES (4, 1)
WITH CTE_Sample (fr, t, level) AS
(
SELECT Table1.fr, Table1.t, 1 AS level
FROM Table1
WHERE fr=1
UNION ALL
SELECT Table1.fr, Table1.t, level+1
FROM Table1
INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t
OPTION (MAXRECURSION 1000)
)

It says Incorrect syntax near the keyword 'OPTION'. What's wrong with it?
Thanks!
|||

You need to use maxrecursion in the projection out of the CTE, the below works for me in SP1;

use tempdb

go

CREATE TABLE Table1 ( fr int, t int)

go

INSERT Table1 VALUES (1, 2)

INSERT Table1 VALUES (2, 3)

INSERT Table1 VALUES (3, 4)

INSERT Table1 VALUES (1, 3)

INSERT Table1 VALUES (1, 4)

INSERT Table1 VALUES (4, 1)

go

WITH CTE_Sample (fr, t, level) AS

(

SELECT Table1.fr, Table1.t, 1 AS level

FROM Table1

WHERE fr=1

UNION ALL

SELECT Table1.fr, Table1.t, level+1

FROM Table1

INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t

)

select *

from CTE_Sample

OPTION (MAXRECURSION 2);

go

|||

Thank you Euan! It works!

btw. Do you know if I can join CTE_Sample with other tables or other CTEs? I tried but got some errors.

WITH CTE_Sample (fr, t, level) AS
(
SELECT Table1.fr, Table1.t, 1 AS level
FROM Table1
WHERE fr=1
UNION ALL
SELECT Table1.fr, Table1.t, level+1
FROM Table1
INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t
)

WITH CTE_length (t, length) AS
(
SELECT t, min(level) AS length
FROM CTE_Sample
where fr=1
group by t
--OPTION (MAXRECURSION 2)
)
select fr, t, level
from CTE_Sample
where CTE_Sample.t=CTE_Path.t and CTE_Sample.level=CTE_path.length

It says "Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
" Then I added a semicolon at the end of CTE_Sample, then it says "Msg 102, Level 15, State 1, Line 11 Incorrect syntax near ';'.
".

What should I do?

|||

I'm not 100% clear on what yuo are actually trying to do but in principle you can use CTEs very flexibly, I suggest reviewing Boks On Line and doing some online searching for them, there are lots of examples.

You can also try looking in the T-SQL forum for examples in there.

|||

You should separate the With statements with a comma and remove the second "with".

WITH CTE_Sample (fr, t, level) AS
(
SELECT Table1.fr, Table1.t, 1 AS level
FROM Table1
WHERE fr=1
UNION ALL
SELECT Table1.fr, Table1.t, level+1
FROM Table1
INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t
) ,

CTE_length (t, length) AS
(
SELECT t, min(level) AS length
FROM CTE_Sample
where fr=1
group by t
--OPTION (MAXRECURSION 2)
)
select fr, t, level
from CTE_Sample
where CTE_Sample.t=CTE_Path.t and CTE_Sample.level=CTE_path.length

Help! Can I control the recursion times of a recursive CTE?

I have a table describing a hierarchy structure and the number of levels is very large, say 10000. Can I control the recursive CTE to get the first 1000 levels? Thanks!

You should be able to with a where clause on some accumulator. For example, take the level column in the following query:

WITH Employee_CTE(EmployeeID, ManagerID, level)
AS
(
SELECT EmployeeID, ManagerID, 1 AS level
FROM HumanResources.Employee as Employee

UNION ALL

SELECT Employee.EmployeeID, Employee.ManagerID, level + 1
FROM HumanResources.Employee as Employee
INNER JOIN Employee_CTE
on Employee.ManagerID= Employee_CTE.EmployeeID
)
SELECT *
FROM Employee_CTE
WHERE level <= 2
ORDER BY level desc

Just vary the value in the where clause to vary the number of times it recurses.

|||

Thank you Louis. But that denpends on the recursion would end properly, and it'll calculate all the levels. If the number of the levels is too large, or there's a loop, there would be a problem. I tried to use the MAXRECURSION hint, but it gave me a syntax error. What I have is:

WITH Employee_CTE(EmployeeID, ManagerID, level)
AS
(
SELECT EmployeeID, ManagerID, 1 AS level
FROM HumanResources.Employee as Employee

UNION ALL

SELECT Employee.EmployeeID, Employee.ManagerID, level + 1
FROM HumanResources.Employee as Employee
INNER JOIN Employee_CTE
on Employee.ManagerID= Employee_CTE.EmployeeID

option (MAXRECURSION 10)
)

Then it told me the syntax for option (MAXRECURSION 10) is wrong. You have any ideas what the right way is?

Thanks!

|||

Yeah, it goes on the query, not the CTE (kind of wierd syntax, but anyhow)

WITH Employee_CTE(EmployeeID, ManagerID, level)
AS
(
SELECT EmployeeID, ManagerID, 1 AS level
FROM HumanResources.Employee as Employee

UNION ALL

SELECT Employee.EmployeeID, Employee.ManagerID, level + 1
FROM HumanResources.Employee as Employee
INNER JOIN Employee_CTE
on Employee.ManagerID= Employee_CTE.EmployeeID

)

SELECT *
FROM Employee_CTE
Option (MAXRECURSION 10)

Help! Can I control the recursion times of a recursive CTE?

I have a table describing a hierarchy structure and the number of levels is very large, say 10000. Can I control the recursive CTE to get the first 1000 levels? Thanks!

Sorry, wrong format in last post. Here's the question:

I have a table describing a hierarchy structure and the number of levels is very large, say 10000. Can I control the recursive CTE to get the first 1000 levels? Thanks!

|||Check out maxrecursion here http://msdn2.microsoft.com/en-us/library/ms175972.aspx|||Thanks. I tried it but got some error:

CREATE TABLE Table1 ( fr int, t int)
INSERT Table1 VALUES (1, 2)
INSERT Table1 VALUES (2, 3)
INSERT Table1 VALUES (3, 4)
INSERT Table1 VALUES (1, 3)
INSERT Table1 VALUES (1, 4)
INSERT Table1 VALUES (4, 1)
WITH CTE_Sample (fr, t, level) AS
(
SELECT Table1.fr, Table1.t, 1 AS level
FROM Table1
WHERE fr=1
UNION ALL
SELECT Table1.fr, Table1.t, level+1
FROM Table1
INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t
OPTION (MAXRECURSION 1000)
)

It says Incorrect syntax near the keyword 'OPTION'. What's wrong with it?
Thanks!|||

You need to use maxrecursion in the projection out of the CTE, the below works for me in SP1;

use tempdb

go

CREATE TABLE Table1 ( fr int, t int)

go

INSERT Table1 VALUES (1, 2)

INSERT Table1 VALUES (2, 3)

INSERT Table1 VALUES (3, 4)

INSERT Table1 VALUES (1, 3)

INSERT Table1 VALUES (1, 4)

INSERT Table1 VALUES (4, 1)

go

WITH CTE_Sample (fr, t, level) AS

(

SELECT Table1.fr, Table1.t, 1 AS level

FROM Table1

WHERE fr=1

UNION ALL

SELECT Table1.fr, Table1.t, level+1

FROM Table1

INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t

)

select *

from CTE_Sample

OPTION (MAXRECURSION 2);

go

|||

Thank you Euan! It works!

btw. Do you know if I can join CTE_Sample with other tables or other CTEs? I tried but got some errors.

WITH CTE_Sample (fr, t, level) AS
(
SELECT Table1.fr, Table1.t, 1 AS level
FROM Table1
WHERE fr=1
UNION ALL
SELECT Table1.fr, Table1.t, level+1
FROM Table1
INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t
)

WITH CTE_length (t, length) AS
(
SELECT t, min(level) AS length
FROM CTE_Sample
where fr=1
group by t
--OPTION (MAXRECURSION 2)
)
select fr, t, level
from CTE_Sample
where CTE_Sample.t=CTE_Path.t and CTE_Sample.level=CTE_path.length

It says "Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
" Then I added a semicolon at the end of CTE_Sample, then it says "Msg 102, Level 15, State 1, Line 11 Incorrect syntax near ';'.
".

What should I do?

|||

I'm not 100% clear on what yuo are actually trying to do but in principle you can use CTEs very flexibly, I suggest reviewing Boks On Line and doing some online searching for them, there are lots of examples.

You can also try looking in the T-SQL forum for examples in there.

|||

You should separate the With statements with a comma and remove the second "with".

WITH CTE_Sample (fr, t, level) AS
(
SELECT Table1.fr, Table1.t, 1 AS level
FROM Table1
WHERE fr=1
UNION ALL
SELECT Table1.fr, Table1.t, level+1
FROM Table1
INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t
) ,

CTE_length (t, length) AS
(
SELECT t, min(level) AS length
FROM CTE_Sample
where fr=1
group by t
--OPTION (MAXRECURSION 2)
)
select fr, t, level
from CTE_Sample
where CTE_Sample.t=CTE_Path.t and CTE_Sample.level=CTE_path.length