SQL server recursive query error.The maximum recursion 100 has been exhausted before statement completion
- by ienax_ridens
I have a recursive query that returns an error when I run it; in other databases (with more data) I have not the problem.
In my case this query returns 2 colums (ID_PARENT and ID_CHILD) doing a recursion because my tree can have more than one level, bit I wanna have only "direct" parent.
NOTE: I tried to put OPTION (MAXRECURSION 0) at the end of the query, but with no luck.
The following query is only a part of the entire query, I tried to put OPTION only at the end of the "big query" having a continous running query, but no errors displayed.
Error have in SQL Server:
"The statement terminated.The maximum recursion 100 has been exhausted before statement completion"
The query is the following:
WITH q
AS (SELECT ID_ITEM,
ID_ITEM AS ID_ITEM_ANCESTOR
FROM ITEMS_TABLE i
JOIN ITEMS_TYPES_TABLE itt
ON itt.ID_ITEM_TYPE = i.ID_ITEM_TYPE
UNION ALL
SELECT i.ID_ITEM,
q.ID_ITEM_ANCESTOR
FROM q
JOIN ITEMS_TABLE i
ON i.ID_ITEM_PADRE = q.ID_ITEM
JOIN ITEMS_TYPES_TABLE itt
ON itt.ID_ITEM_TYPE = i.ID_ITEM_TYPE)
SELECT ID_ITEM AS ID_CHILD,
ID_ITEM_ANCESTOR AS ID_PARENT
FROM q
I need a suggestion to re-write this query to avoid the error of recursion and see the data, that are few.
