Snowflake DB unable to recognize "OPTION (MAXRECURSION 1000)" used in a CTE Recursion Query

user_13915049 0
Viewed 683

I have a created a CTE recursion query which will display rows as shown below. This below format is the required output and that's acheived.

Tbale

Here is the query

with Numbers AS
(
    SELECT DateAdd(day, 1, '2030-05-26') AS DateAdd
  union all
  select DateAdd(day, 1, DateAdd)
  FROM Numbers
    WHERE DateAdd(day, 1, DateAdd) <= '2030-06-05'
),
CT as ( select Product ID, Product Name, Status Flag, Load Date from  
  "Table"."TBL_PRODUCT_CONTROL" where load_date between  '2020-11-06' and '2020-11-06' order by Load Date DESC )
SELECT  Product ID, Product Name, DATE(DateAdd) as Load Date, Status Flag
FROM Numbers  LEFT JOIN CT;

The objective is to use a TABLE named TBL_PRODUCT_CONTROL and map its fields to all the dates starting from 2030-05-27 till 2090-05-26. The product Ids, Names, Flags must repeat for 'n' number of chosen future dates.

The query is working fine for short date ranges like a month, and fails when trying to load dates beyond a year or so. snowflake is not allowing me to use more than 100 recursions using the above query. The only possible way is to find an alternative by to disable the recursion limit snowflake has, or to try to do smaller date ranges like from 2030-05-27 till 2030-06-05, which is not that great and useful.

Hence I have tried by putting the statement OPTION (MAXRECURSION 1000) at the end of the CTE query to allow 1000 iterations. But snowflake is unable to recognize it throwing the below error.

SQL compilation error: syntax error line 13 at position 48 unexpected '1000'.

How can I mitigate this problem ? What other approaches can I try to load a Dates till 2100-01-01. Help me here.

snowflake-cloud-data-platform common-table-expression sql
1 Answers

The Snowflake documentation is pretty clear that this is set at the account level, so you cannot adjust it in a query.

You can generate dates using other methods. For instance:

select dateadd(day, row_number() over (order by null),
               '2030-05-26')
              ) as date
from table (generator(rowcount => (10000)));

I don't know what the right value of "10000" is for your purposes.

user_11440350
Related