Can i do a inner "With" inside another "With" in SQL?

Question

I'm trying to use multiple SQL With clauses.

The reason of me using multiple With is that I'm sending this SQL to a AS400 project. The With TEMP has to be obligatory instead of Temp2 that has to be optional.

I can't figure out how to do it. This SQL still throws an error:

With Temp2 As 
(
    With Temp As 
    (
        Select Name, Surname, Age 
        From People
        Where Age > 18
    )
    Select A.*, B.* 
    From Temp A 
    Left Join City B on B.Name = A.Name 
                     and B.Surname = A.Surname 
    Where B.City = "Venice"
)
Select * 
From Temp2 C 
Left Join State D on D.City = C.City

I'd like to understand how I can do something like that.

Answer 1

Yes, any CTE can reference a CTE that is created before it. The first CTE must be prefaced by "With" and terminated with a comma, which allows for another CTE to be created.

with temp as 
(
    select name, surname, age 
    from people
    where age > 18
),
temp2 as 
    (
        select a.*, b.* 
        from temp a 
        left join city b 
            on b.name = a.name 
            and b.surname = a.surname 
        where b.city = "Venice"
    )

select * 
from temp2 c 
left join state d 
    on d.city = c.city
;

This is functionally equivalent to the query below, which does not require any CTE's.

select *
from people as a
join city b 
    on b.name = a.name
    and b.surname = a.surname 
    and b.city = "Venice"
left join state c
    on c.city = b.city
where a.age > 18
;