Can You Create Nested with Clauses for Common Table Expressions

Can you create nested WITH clauses for Common Table Expressions?

While not strictly nested, you can use common table expressions to reuse previous queries in subsequent ones.

To do this, the form of the statement you are looking for would be

WITH x AS 
(
    SELECT * FROM MyTable
), 
y AS 
(
    SELECT * FROM x
)
SELECT * FROM y

Truly nested CTEs in T-SQL / SQL Server

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.

nested WITH statement

you can do it as follows:

; with res1 as (
        select 1 as col1
    ),
    res2 as(
    select * from res1
    )
select * from res2

In-line CTE in SQL

Not only that, you can have CTEs inside of CTEs.

WITH CTE1 AS (
  WITH CTE2 AS (
    …
  )
)
SELECT …

I had to work on someone’s query that did this 13 levels deep. What a pain. The main reason this isn’t good is that it makes a mess of readability. I think what you are describing isn’t quite as bad as it keeps subsections of the query together. Doing oddball things like this generally leads to confusion when others try to understand your query.

Redshift - Create Table based on Nested Common Table Expressions

Found the answer while writing my question: A table can be created based on nested CTE's by adding the following syntax to the example query.

CREATE TEMPORARY TABLE testtable1 as (
   WITH allCustomers as (SELECT Customerid FROM Customer_tbl),
    customer_purchasing as (SELECT Customerid, 
                                   Item, 
                                   Price 
                            FROM allCustomers 
                            JOIN purchases_tbl 
                              ON allCustomers.Customerid = purchases_tbl.Customerid)
SELECT * FROM customer_purchasing) 

When to use Common Table Expression (CTE)

One example, if you need to reference/join the same data set multiple times you can do so by defining a CTE. Therefore, it can be a form of code re-use.

An example of self referencing is recursion: Recursive Queries Using CTE

For exciting Microsoft definitions
Taken from Books Online:

A CTE can be used to:

• Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.

• Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.

• Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.

• Reference the resulting table multiple times in the same statement.

How can I have multiple common table expressions in a single SELECT statement?

I think it should be something like:

WITH 
    cte1 as (SELECT * from cdr.Location),
    cte2 as (SELECT * from cdr.Location)
select * from cte1 union select * from cte2

Basically, WITH is just a clause here, and like the other clauses that take lists, "," is the appropriate delimiter.

Syntax for nested common-table-expression

You had a few syntax errors including a UNION ALL that was not needed at the bottom, and your first SELECT in the allhours was referencing an alias, so try this:

;with cte as 
(
  select dateadd(hour, 1, cast(cast(getdate() -1 as date) as datetime)) as midnight
),
allhours as 
(
  select 0 as hour, midnight as timestart, dateadd(hour, 1, midnight) as timeend 
  from cte 
  union all
  select 1 as hour, dateadd(hour, 1, midnight), dateadd(hour, 2, midnight) 
  from cte 
  union all
  select 23 as hour, dateadd(hour, 23, midnight), dateadd(hour, 24, midnight) 
  from cte
) 
select *
from allhours

see SQL Fiddle with Demo

---

Related Topics