CODEWITHSUNDEEP
recursionsnowflake-cloud-data-platform
Yadhu
Yadhu

Reputation: 125

Snowflake - Recursion exceeded max iteration count (100)

WITH recursive T1(USER_NAME,ID, PARENT_ID, LVL, ROOT_ID, PATH) AS (
-- ANCHOR MEMBER.
SELECT USR_NM,USR_NO,
      MNGR_EMPLY_NBR PARENT_ID,
      1 AS LVL,
      USR_NO AS ROOT_ID,
      TO_CHAR(FRST_NM || ' ' || LST_NM) AS PATH
FROM    EMPLOYEE

UNION ALL

-- RECURSIVE MEMBER.
SELECT T2.USR_NM,T2.USR_NO,
        T2.MNGR_EMPLY_NBR  PARENT_ID,
      LVL+1,
      T1.ROOT_ID,
      T1.PATH || '|' || T2.FRST_NM || ' ' || T2.LST_NM AS PATH
FROM   EMPLOYEE T2 , T1
WHERE  T2.MNGR_EMPLY_NBR = T1.ID
)
select * from T1

While running the above code I'm facing Recursion exceeded max iteration count (100) in snowflake. Can anyone guide me with a solution for this or is there any way to re-write this code without recursion?

Upvotes: 0

Views: 1108

Answers (2)

Tomáš Fejfar
Tomáš Fejfar

Reputation: 11217

The limit is about to be removed as part of 2022_02 change bundle.

https://community.snowflake.com/s/article/Hierarchical-Data-Queries-Iteration-Limits-No-Longer-Enforced

Here is the pending behaviour change log: https://community.snowflake.com/s/article/Pending-Behavior-Change-Log

2022_02

Disabled by default in 6.7 (Mar 9-10); can be enabled for testing

Planned to be enabled by default in March

Upvotes: 1

Michael Golos
Michael Golos

Reputation: 2059

This is standard infinite recursion protection. You can read about it here: Potential for Infinite Loops

You must submit a request to Snowflake Support to increase the limit and enter how many.

Referring to the documentation:

In theory, constructing a recursive CTE incorrectly can cause an infinite loop. In practice, Snowflake prevents this by limiting the number of iterations that the recursive clause will perform in a single query. The MAX_RECURSIONS parameter limits the number of iterations.

To change MAX_RECURSIONS for your account, please contact Snowflake Support.

Upvotes: 1

Related Questions