Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (20.3k points)

I am in the process of simplifying a complicated select statement, so thought I would use common table expressions.

Declaring a single cte works fine.

WITH cte1 AS (

    SELECT * from cdr.Location

    )

select * from cte1 

Is it possible to declare and use more than one cte in the same SELECT?

ie this SQL gives an error

WITH cte1 as (

    SELECT * from cdr.Location

)

WITH cte2 as (

    SELECT * from cdr.Location

)

select * from cte1    

union     

select * from cte2

the error is

Msg 156, Level 15, State 1, Line 7

Incorrect syntax near the keyword 'WITH'.

Msg 319, Level 15, State 1, Line 7

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an XML namespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

NB. I have tried putting semicolons in and get this error

Msg 102, Level 15, State 1, Line 5

Incorrect syntax near ';'.

Msg 102, Level 15, State 1, Line 9

Incorrect syntax near ';'.

Probably not relevant but this is on SQL 2008.

1 Answer

0 votes
by (40.4k points)

Try using the code given below:

WITH 

    cte1 as (SELECT * from cdr.Location),

    cte2 as (SELECT * from cdr.Location)

select * from cte1 union select * from cte2

In the above query, WITH is just a clause, and like the other clauses that take lists, "," is the appropriate delimiter.

Welcome to Intellipaat Community. Get your technical queries answered by top developers!

29.3k questions

30.6k answers

501 comments

104k users

Browse Categories

...