CODEWITHSUNDEEP
sqlsql-serveroracle-databasecommon-table-expression
Sravan
Sravan

Reputation: 193

Query on WITH Clause in SQL Server

I have a example query(fully modified) as below:

with projects as (

select * from projectdetails

)
select * from projects

when I run the above query, it is running fine.

But when i put one more select query..it is throwing error.

select * from 
(

with projects as (

select * from projectdetails


)

select * from projects

)

Error: 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.

The same query runs fine in Oracle but not in SQL server.

Upvotes: 0

Views: 13695

Answers (4)

Lukasz Szozda
Lukasz Szozda

Reputation: 176189

It doesn't matter what runs on Oracle, see supported SQL Server SELECT syntax

Simplified:

[ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> ] } ]

SELECT select_list [ INTO new_table ]

[ FROM table_source ] [ WHERE search_condition ]

[ GROUP BY group_by_expression ]

[ HAVING search_condition ]

[ ORDER BY order_expression [ ASC | DESC ] ]

Full:

<SELECT statement> ::=  
    [ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> [,...n] ] } ]
    <query_expression> 
    [ ORDER BY { order_by_expression | column_position [ ASC | DESC ] } 
  [ ,...n ] ] 
    [ <FOR Clause>] 
    [ OPTION ( <query_hint> [ ,...n ] ) ] 
<query_expression> ::= 
    { <query_specification> | ( <query_expression> ) } 
    [  { UNION [ ALL ] | EXCEPT | INTERSECT }
        <query_specification> | ( <query_expression> ) [...n ] ] 
<query_specification> ::= 
SELECT [ ALL | DISTINCT ] 
    [TOP ( expression ) [PERCENT] [ WITH TIES ] ] 
    < select_list > 
    [ INTO new_table ] 
    [ FROM { <table_source> } [ ,...n ] ] 
    [ WHERE <search_condition> ] 
    [ <GROUP BY> ] 
    [ HAVING < search_condition > ]

If you don't know how to read this syntax directly you can generate Railroad Diagrams for every syntax definition from BOL examples below:

enter image description here enter image description here

Upvotes: 1

M.Ali
M.Ali

Reputation: 69574

As other two answers speak about the syntax of CTE and their limitation, here is a way you would do this what you are trying to do, Multiple CTEs.

with projectdetails as (

select * from projectdetails


),projects AS

select * from projects

)
SELECT * FROM projects  --<-- here you select from Projects or projectdetails

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172628

The query which you are trying to execute is called Common Table Expression and the the syntax which you are using is not the proper way of using it ie, you cannot use the WITH clause inside the select statement.

with projects as (

select * from projectdetails

)
select * from projects

The above query is fine and will work and the next query which you posted is neither correct and neither it is making sense as it is looking as if you are trying to do the same thing as above.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271051

SQL Server and Oracle have different syntax requirements for common table expressions.

In Oracle, these can come before any SELECT. In SQL Server, they need to come at the beginning of the query. Hence, you cannot have a subquery with WITH in it.

Usually, you can just move the WITH statement before the first SELECT and the query will work in both databases.

Upvotes: 2

Related Questions