CODEWITHSUNDEEP
sqlsql-servert-sql
JOE SKEET
JOE SKEET

Reputation: 8118

using CTE (common table expressions) in a query

with cte as
(
select rowid from batchinfo where datapath like '%thc%'
)

select * from qvalues where rowid in cte

i am getting this error:

Msg 102, Level 15, State 1, Line 6 Incorrect syntax near 'cte'.

does anyone know what am doing wrong?

Upvotes: 3

Views: 629

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

As casually alluded to in Al W's answer (and Tony's comment). The fact that the error is being described as occurring on line 6 means that it's not the first statement in the batch. Which means that you need to have a semicolon before the WITH keyword:

When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

Also, from Transact-SQL Syntax conventions:

Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version.

So it's worth getting in the habit of putting the semicolons in.

Upvotes: 1

Al W
Al W

Reputation: 7713

you are treating a CTE as a subquery, where instead it should be used more like a table.

try this

;with cte as
(
select rowid from batchinfo where datapath like '%thc%'
)
select * from qvalues 
INNER JOIN cte on cte.rowid=qvalues.rowid

Upvotes: 8

Related Questions