Using CTE (common table expressions) in a query

Question

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

select * from qvalues where rowid in cte

I get this error:

Msg 102, Level 15, State 1, Line 6 Invalid syntax next to 'cte'.

Does anyone know what I'm doing wrong?

Answer 1

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

try it

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

Answer 2

As accidentally mentioned in Al W's answer (and Tony's comment). The fact that the error is described as occurring on line 6 means that this is not the first operator in the batch. This means that you need to have a semicolon before the WITH keyword:

CTE , , .

, Transact-SQL:

SQL Server, .

.