Truly nested CTEs in T-SQL / SQL Server

Question

From Postgres I am used to do:

with a as (
 with b as (
  SELECT
  ...
 )
 SELECT
 ...
),
c as (
 SELECT
 ...
)
SELECT
...

This type of nesting (or CTE hierarchy) of a & b does not execute in T-SQL however.

Is there a workaround for this, a simple rewrite as suggested in Can you create nested WITH clauses for Common Table Expressions? like

with a as (
 SELECT
 ...
 ),
b as (
 SELECT
 ...
),
c as (
 SELECT
 ...
)
SELECT
...

is not possible in my case (see query below), as in many other cases.

Would be glad for some pointers since I am new to T-SQL.

--generate a time-series in 15min intervals, ending with the last incoming user
WITH last_timeslot AS (
--get the last ts from one of the two tables with less recent entries
    SELECT
        dateadd(
            MINUTE,
                datediff(
                    MINUTE,
                    0,
                    LEAST(
                        (SELECT
                            MAX(ts)
                        FROM dbo.user u )
                        ,
                        (SELECT
                            MAX(ts)
                        FROM dbo.user_history uh )
                    )
                )/ 15 * 15, --round to 15min slot
            0
        ) AS last_timeslot_ts
),
timeslot_series AS (
    --recursively generate a timeseries going back 14 days from the last timeslot, in 15min steps
    WITH cte AS (
      SELECT DATEADD(DAY,-14,(SELECT last_timeslot_ts FROM last_timeslot)) AS slot
      UNION ALL
      SELECT  DATEADD(MINUTE,15,slot) 
      FROM    cte   
      WHERE   DATEADD(MINUTE,15,slot) < (SELECT last_timeslot_ts FROM last_timeslot)
    )
    SELECT  slot
    FROM    cte
    OPTION (MAXRECURSION 0)
),
c as (
--other stuff happens
SELECT
...
FROM timeslot_series
LEFT JOIN
...
)
SELECT ...

You can't nest CTEs, as you've been advised previously. You need to define them like you were shown and you demonstrate in the second example. Also `OPTION` goes at the *very end* of your query, not the middle of it. There's, however, no need for an rCTe here; use a Tally they are *significantly* faster. Perhaps if you get rid of the recursion problem your misunderstanding will be eliminated. Finally, there is no `LEAST` function in T-SQL. — Thom A, Jul 23 '21 at 16:50
Why rewrite is not possible? Such nesting is quite unreadable, so it is a good time for redesign — astentx, Jul 23 '21 at 16:53
In my opinion, the first way of nesting CTEs makes no sense to begin with. What are you trying to achieve with that? — , Jul 23 '21 at 17:39

Joel Coehoorn · Accepted Answer · 2021-07-23T20:11:53.350

I'll simplify to two levels of nesting, because it's enough to demonstrate the solution. We start with this:

with a as (
 with b as (
  SELECT
  ...
 )
 SELECT
 ...
),
SELECT
...

Presumably, a needs to reference b as part of it's query. So when we try to un-nest them and instead list them in serial as below it doesn't work yet:

with a as (
 SELECT
 ...
 ),
b as (
 SELECT
 ...
),
SELECT
...

To fix this, we need to reverse the order in which we define each CTE. That is, define the inner-most levels of the query as a CTE first, and then work outwards:

with b as (
 SELECT
 ...
 ),
a as (
 SELECT
 ...
),
SELECT
...

Now you will be able to reference b within a (and again in the final SELECT, if needed). If we were to go back to the original example and include c again, it would now come first, followed by b, and then a.

Truly nested CTEs in T-SQL / SQL Server

1 Answers1