Is it possible in SQL to use WITH inside a WITH

Question

In SQL, is it possible to place a WITH inside a WITH?

Take the below query for example,

WITH Temp ([Description], [Amount], [OverdueBy])
AS 
(select Description, SUM(Amount) as Amount, (DATEDIFF(day,DueDate,GETDATE())) as OverdueBy  from brvAPAllInvoices 
Where PaidDate is null and APCo = 1 and Amount > 0 
Group By Description, DueDate, APRef

)

select * from Temp

I want to create a "virtual" temporary table based off the above query. Is it possible to use another WITH to contain it in?

Something along the lines of this:

WITH Temp2 ([Description], [Amount], [OverdueBy])
AS
(
WITH Temp ([Description], [Amount], [OverdueBy])
AS 
(select Description, SUM(Amount) as Amount, (DATEDIFF(day,DueDate,GETDATE())) as OverdueBy  from brvAPAllInvoices 
Where PaidDate is null and APCo = 1 and Amount > 0 
Group By Description, DueDate, APRef

)

select * from Temp)

select * from Temp2

Answer 1

Depending on your dbms, you can have multiple WITH statements, nested or not. (Illustrated with PostgreSQL.) SQL Server doesn't allow nesting common table expressions. (Search for CTE_query_definition.)

Nested

with today as (
  with yesterday as (select current_date - interval '1' day as yesterday)
  select yesterday + interval '1' day as today from yesterday 
)
select cast(today as date) from today

today
--
2014-06-11

When you nest common table expressions, the nested CTE isn't visible outside its enclosing CTE.

with today as (
  with yesterday as (select current_date - interval '1' day as yesterday)
  select yesterday + interval '1' day as today from yesterday 
)
select * from yesterday

ERROR: relation "yesterday" does not exist

Unnested

with yesterday as (
  select current_date - interval '1' day as yesterday
), 
today as (
  select yesterday + interval '1' day as today from yesterday 
)
select cast(yesterday as date) as dates from yesterday
union all
select cast(today as date) from today

dates
--
2014-06-10
2014-06-11

When you use successive, unnested CTEs, the earlier ones are visible to the later ones, but not vice versa.

with today as (
  select yesterday + interval '1' day as today from yesterday 
),
yesterday as (
  select current_date - interval '1' day as yesterday
) 
select yesterday from yesterday
union all
select today from today

ERROR:  relation "yesterday" does not exist

Answer 2

No, you can't define a CTE within a CTE, however you can define multiple CTE's and reference other CTE's in a single statement.

; with a as (
    select * from some_table
),
b as (
   select * 
   from another_table t
     inner join a ON (t.key = a.key)
)
select * 
from b