Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mapidea
Posting Yak Master
124 Posts |
 Posted - 2010-01-02 : 18:24:44
|
| I want to solve this puzzle that why this goes into infinite loop. I missing the core part of how CTE works. WITH cte (EmployeeID, ManagerID, Title) as( SELECT EmployeeID, ManagerID, Title FROM HumanResources.Employee WHERE ManagerID IS NOT NULL UNION ALL SELECT cte.EmployeeID, cte.ManagerID, cte.Title FROM cte JOIN HumanResources.Employee AS e ON cte.ManagerID = e.EmployeeID)SELECT EmployeeID, ManagerID, TitleFROM cte |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-02 : 18:54:42
|
The first select in your cte should be the anchor hence: WHERE ManagerID IS NULL.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-02 : 19:06:03
|
and this:ON cte.ManagerID = e.EmployeeIDshould be:ON e.ManagerID = cte.EmployeeID
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
mapidea
Posting Yak Master
124 Posts |
Posted - 2010-01-02 : 19:22:55
|
| Thanks for your reply.Can you explain it by example how the query is processed.Thanks a lot. |
|
|
|
mapidea
Posting Yak Master
124 Posts |
Posted - 2010-01-02 : 19:45:16
|
| This link says it all...... Thanks Fredhttp://msdn.microsoft.com/en-us/library/ms186243.aspx |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-03 : 08:01:12
|
welcome
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
Infinite Loop in CTE