support nested WITH statements (i.e. nesting of SELECT statements with WITH clauses inside of a CTE)

The query below does not work in any TSQL-db. But it does on every other database today that I can think of. ``` -- does not work WITH goku_outer AS ( WITH goku_inner AS ( SELECT 9001 as power_level ) SELECT * FROM goku_inner ) SELECT * FROM goku_outer ``` Allowing nested WITH statements (not in love with terminology, examples given below) would enable MSFT customers to fully take advantage of templating engines and their respective ecosystems. These templating engines are already very popular on non-MSFT database products. For a longer description please see this link https://dbt-msft.github.io/dbt-msft-docs/docs/nested_CTES/

New

ProgrammabilitySuggestions

2 Comments

You must to comment

Daniel Jameson3 years ago

0 Reports0

These are other WITH scenarios that I tried and did not work: • set @MyVar = (WITH goku AS (SELECT 9001 as power_level) SELECT power_level FROM goku); • if exists (WITH goku AS (SELECT 9001 as power_level) SELECT power_level FROM goku) begin... end; The first one can be rewritten as this: WITH goku AS (SELECT 9001 as power_level) SELECT @MyVar =(SELECT power_level FROM goku); I have not found a way implement the second one.

Alex Butler3 years ago

0 Reports0

The devs need to understand the criticality of aligning themselves with these technologies that are being taken up and adopted worldwide. As emerging tech continues to be able template and revision SQL, ephemerally interpolating statements, etc. The gains are too big to ignore such a feature as we all see the direction we are going. Especially when acknowledging this drawback from potential customers in other database systems using dbt who find out that their models break down here and can no longer buy in.