CODEWITHSUNDEEP
sqlview
pgunston
pgunston

Reputation: 302

Is it possible in SQL to use WITH inside a WITH

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

Upvotes: 3

Views: 5275

Answers (3)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95532

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

Upvotes: 3

Mr Moose
Mr Moose

Reputation: 6344

You'd typically do something like the following;

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
), Temp2 ([Description], [Amount], [OverdueBy]) AS 
(
    SELECT * FROM Temp
)
select * from Temp2

Althouth in your contrived example, there obviously isn't much use as both table structures are pretty much the same. I tend to think of CTEs more as named derived tables than temporary tables too....even though they are much more than just a derived table

Upvotes: 0

Darren Kopp
Darren Kopp

Reputation: 77627

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

Upvotes: 2

Related Questions