CODEWITHSUNDEEP
sql-serverrecursionrecursive-query
ienax_ridens
ienax_ridens

Reputation: 255

SQL server recursive query error.The maximum recursion 100 has been exhausted before statement completion

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.

Upvotes: 1

Views: 9084

Answers (2)

mahesh
mahesh

Reputation: 101

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
OPTION  ( MAXRECURSION 500 )

Upvotes: 0

ienax_ridens
ienax_ridens

Reputation: 255

Ok, after some investigations, for some reason, there were a circular references in 2 records: ITEM 1 was child of ITEM 2 and ITEM 2 was child of ITEM 1.

Changing manually the values, query run perfectly.

Upvotes: 1

Related Questions