September 17, 2011 at 11:52 pm
Hello:
in sql server 2008 r2 i have a table 'Table_One' ,this table have 3 fields : 1 - Id, 2 - Name , 3 - ParentId
I add below data to 'Table_One':
153110806001 JoeNULL
153110806002 JackNULL
153110806003 FrankNULL
153110806004 Bill153110806001
153110806005 Brad153110806001
153110806006 John153110806001
153110806007 David153110806001
153110909008 RayanNULL
153110909009 Pohl153110909008
153110909010 MarkNULL
153110909011 LeoNULL
153110910014 Antony153110806001
153110910015 Paolo153110806001
153110910016 Andy153110806001
153110910017 George153110806001
And write this query:
WITH CTE AS (
SELECT [Id]
,[Name]
,[ParentId]
FROM [Table_One]
WHERE ParentId IS NULL
UNION ALL
SELECT e.[Id]
,e.[Name]
,e.[ParentId]
FROM [Table_One] e
INNER JOIN CTE cte ON cte.Id = e.Id
)
SELECT *
FROM CTE OPTION
When I Run Above Query I get this error:
'Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.'
Why this happen?
September 18, 2011 at 1:03 am
Eskandari (9/17/2011)
Hello:in sql server 2008 r2 i have a table 'Table_One' ,this table have 3 fields : 1 - Id, 2 - Name , 3 - ParentId
I add below data to 'Table_One':
153110806001 JoeNULL
153110806002 JackNULL
153110806003 FrankNULL
153110806004 Bill153110806001
153110806005 Brad153110806001
153110806006 John153110806001
153110806007 David153110806001
153110909008 RayanNULL
153110909009 Pohl153110909008
153110909010 MarkNULL
153110909011 LeoNULL
153110910014 Antony153110806001
153110910015 Paolo153110806001
153110910016 Andy153110806001
153110910017 George153110806001
And write this query:
WITH CTE AS (
SELECT [Id]
,[Name]
,[ParentId]
FROM [Table_One]
WHERE ParentId IS NULL
UNION ALL
SELECT e.[Id]
,e.[Name]
,e.[ParentId]
FROM [Table_One] e
INNER JOIN CTE cte ON cte.Id = e.parentid
)
SELECT *
FROM CTE OPTION
When I Run Above Query I get this error:
'Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
Why this happen?
I havev corrected yout original query in the quote. Your join was joining the wrong column.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply