sql with clause within a with clause

Question

can i do something like this :

with t as 
    (
        with tt as
            ( 
                 select * from table 
            )
        SELECT * FROM tt
    )
select * from t

i willing to perform some logic on output of inner with clause & than again do some operations on output of the outer with clause.

any help will be appreciated ...
Thanks

note :- its just some simplified query that will resolve my problem in my actual query, which have nested with clause

Giannis Paraskevopoulos · Answer 1 · 2013-08-02T07:27:47.790

38

You can do something like this:

with t as 
(
    select * from table
),
tt as
( 
     select * from t
)
select * from tt

edited Aug 02 '13 at 07:27

answered Aug 02 '13 at 07:13

do u mean `select * from table` in 7th line ? – objectWithoutClass Aug 02 '13 at 07:21
This is the way to do it! Thanks – Marc Giombetti Feb 14 '14 at 12:39

marc_s · Answer 2 · 2013-08-02T07:26:52.237

14

No, you cannot nest CTE (Common Table Expression) but you can chain them:

with t as 
(
    select * from table 
),
tt as
( 
    select * from t
)
SELECT * FROM tt

edited Aug 02 '13 at 07:26

answered Aug 02 '13 at 07:13

marc_s

in first clause i can write some select query ? – objectWithoutClass Aug 02 '13 at 07:21
@objectWithoutClass: **SURE!** Just go ahead, try it! – marc_s Aug 02 '13 at 07:26

2 Answers2