Skip to content

Feature Request: support OPTION( MAXRECURSION <INT> ) for recursive CTE's #104

Description

@JustGitting

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/):

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?

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions