WITH expression_name (column_list)
AS
(
-- Anchor member
initial_query
UNION ALL
-- Recursive member that references expression_name.
recursive_query
)
-- references expression name
SELECT *
FROM expression_name
OPTION( MAXRECURSION 200 )
However, even when I materialized the model as a table, I get the following error:
Incorrect syntax near the keyword 'OPTION'.
This is because the way DBT creates the model. DBT will create a view and then copy the results into a table.
If I remove option(MAXRECURSION, 200), then I get the following error when running DBT:
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
Could the OPTION() directive be supported in tsql-utils?
Or it is not possible due to the upstream limitation of DBT itself?
If this feature cannot be supported, is there a work-around?
I'm migrating some existing SQL code that uses recursive CTE with the option( MAXRECURSION 200) into DBT.
For example the original code is of the format (copied from https://www.sqlservertutorial.net/sql-server-basics/sql-server-recursive-cte/):
However, even when I materialized the model as a table, I get the following error:
Incorrect syntax near the keyword 'OPTION'.
This is because the way DBT creates the model. DBT will create a view and then copy the results into a table.
If I remove option(MAXRECURSION, 200), then I get the following error when running DBT:
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
Could the OPTION() directive be supported in tsql-utils? Or it is not possible due to the upstream limitation of DBT itself?
If this feature cannot be supported, is there a work-around?