pgunston pgunston - 2 months ago 6
SQL Question

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

Answer

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