Menu
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 xmlnamespaces 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.
625
After learning common table expressions or CTEs, a natural question is “Can I use several CTEs in one query?” Yes, you can!
The same steps for adding a second CTE can be repeated to add a third, fourth, or more CTE queries. The steps to add a second CTE into a query are: Add a comma at the end of the first CTE, after the closing parentheses. After the comma, on the next line, declare the name of the new CTE.
Unlike a derived table, a CTE behaves more like an in-line view and can be referenced multiple times in the same query. Using a CTE makes complex queries easier to read and maintain. Because a CTE can be referred to multiple times in a query, syntax can be simpler.
I think it should be something like:
WITH cte1 as (SELECT * from cdr.Location), cte2 as (SELECT * from cdr.Location) select * from cte1 union select * from cte2 Basically, WITH is just a clause here, and like the other clauses that take lists, "," is the appropriate delimiter.
96
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
