Invalid object name for cte

Question

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.

@FelixPamittan Yes its pointing to both places i am using cte. — D B, May 26 '16 at 07:56
There must be something wrong with the `SELECT` statement itself. Try running the `SELECT` inside the cte. — Felix Pamittan, May 26 '16 at 07:58
Is this part of a larger script? If you add a semi-colon so that `WITH` becomes `;WITH` does that fix the problem? — Dave Sexton, May 26 '16 at 07:59
@DonaldBury Have a look at [this explanation](http://stackoverflow.com/questions/6938060/common-table-expression-why-semicolon), why you have to use `;WITH`. — diiN__________, May 26 '16 at 08:04
[Check it out](http://stackoverflow.com/questions/8004322/when-should-i-not-use-a-semicolon). `;WITH` is ugly. Using `;` to terminate every T-SQL statement is a good practice even not required by syntax. It makes code consistent, easier to read and maintain. There still be more and more statements requiring ';' in future version. Don't get yourself into trouble just because of semicolon. — qxg, May 27 '16 at 04:21

score 4 · Accepted Answer · answered May 26 '16 at 08:25

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.

I'm still getting this error while having the semicolon in the preceding command or in front of WITH. — Sanushi Salgado, May 19 '23 at 07:23

score 1 · Answer 2 · answered May 26 '16 at 08:03

1

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

answered May 26 '16 at 08:03

D B

296
6
27

Invalid object name for cte

2 Answers2