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?
CodePudding user response：
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.
CodePudding user response：
The limit is about to be removed as part of 2022_02 change bundle.