CODEWITHSUNDEEP
sqlsql-servert-sqlssmscommon-table-expression
D B
D B

Reputation: 306

Invalid object name for cte

I am getting the following error when trying to run this cte

Invalid Object Name 'cte'

error when using the following CTE statement

WITH cte (LOC_ID, [Description], LOC_TYPE)
AS
(
    select LOC_ID, DESCR, LOC_TYPE
    FROM [APOLLO].[dbo].[TBL_STATIC_DATA_LOCATIONS_MERLIVE]
    WHERE LOC_TYPE = 'DC'
) 
select * from cte

I am using Microsoft SQL Server Management Studio.

Upvotes: 2

Views: 3954

Answers (2)

Paolo
Paolo

Reputation: 2254

the definition of a CTE requires that the command preceding the CTE itself must be terminated by a semicolon (msdn documentation, remarks, 6th) so if your code is part of a batch:

SELECT a, c, f FROM TABLE; -- <--- mind this semicolon

WITH cte (LOC_ID, [Description], LOC_TYPE)
AS
(
    select LOC_ID, DESCR, LOC_TYPE
    FROM [APOLLO].[dbo].[TBL_STATIC_DATA_LOCATIONS_MERLIVE]
    WHERE LOC_TYPE = 'DC'
) 
select * from cte;

the usual trick is to write ;WITH but the correct way is to terminate the commands with a semicolon.

Upvotes: 5

D B
D B

Reputation: 306

Missing semi-colon ';'

;WITH cte (LOC_ID, [Description], LOC_TYPE)
AS
(

select LOC_ID,DESCR,LOC_TYPE
FROM [APOLLO].[dbo].[TBL_STATIC_DATA_LOCATIONS_MERLIVE]
WHERE LOC_TYPE = 'DC'

)

SELECT * FROM cte

Upvotes: 1

Related Questions