Tuesday, March 25, 2008

No anchor member was specified for recursive query *

I got this error while working on a recursive CTE query, I entered the error message in google/live and got zero results so I had to do some more study on CTEs and I thought I'd share the answer to this problem.

From the MS documentation we read:
The first invocation of the recursive CTE consists of one or more CTE_query_definitions joined by UNION ALL, UNION, EXCEPT, or INTERSECT operators. Because these query definitions form the base result set of the CTE structure, they are referred to as anchor members.
CTE_query_definitions are considered anchor members unless they reference the CTE itself. All anchor-member query definitions must be positioned before the first recursive member definition, and a UNION ALL operator must be used to join the last anchor member with the first recursive member.

Basically you cannot make a recursive call before you have defined the initial data set, let's see with an example:

This would be valid:

with example(col1, col2, iteration) as
(
--first, we defined the base set
select col1, col2 from someTable where col1 is null
union all
--then we can make the recursive call
select t1.col1, t1.col2
from example t1 inner join SomeTable t2 on t1.Col2 = t2.Col1
)
select * from example

and this would generate the error

with example(col1, col2, iteration) as
(
select t1.col1, t1.col2
from example t1 inner join SomeOtherTable t2 on t1.Col2 = t2.Col1
--ERROR:we have not defined a base set before making the recursive call
union all
select col1, col2 from someTable where col1 is null
)
select * from example

Hope this helps someone out there

No comments: