Friday, February 15, 2008

No anchor member was specified for recursive query Problem

Recently I've been playing around with Common Table Expressions (CTE) and I've had a little difficulty figuring out exactly what the syntax is for it.
The main problem I was having was figuring out how the recursive member integrates with the CTE table. I kept getting the following error.

No anchor member was specified for recursive query "MemberRelation".

After a few hours of troubleshooting I realized that I didnt fully understand what the first line of code for the CTE was doing. In actuality, it is describing the table layout of the result set. (Not a table in the database.) Now the expression is working and making sense:

with MyRelation
(
ID1,
ID2,
Level
)
as
(
-- Anchor member definition
select ID1
,ID2
,0 as Level
from relation as e
UNION ALL
-- Recursive member definition
select e.ID1
,e.ID2
,Level + 1
from relation as e
join MyRelation R on R.ID2 = e.ID1
)
-- Statement that executes the CTE
select top 1 * from MyRelation
where Level = 3
option(Maxrecursion 3)
GO

No comments: