CODEWITHSUNDEEP
sqlsql-server
beshr
beshr

Reputation: 293

Msg 208, Level 16, State 1, Line 1 Invalid object name

I'm trying to SELECT from SalesData that created using WITH, the query is 

    with SalesData (TotalSold, OrderYear, TerritoryName)
    AS (

    SELECT SUM(soh.TotalDue) AS 'TotalSold'
        , YEAR(soh.OrderDate) AS 'OrderYear'
        , st.Name AS 'TerritoryName'
    FROM Sales.SalesOrderHeader AS soh
    INNER JOIN Sales.SalesTerritory AS st
    ON soh.TerritoryID = st.TerritoryID
    GROUP BY YEAR(soh.OrderDate)
            ,st.Name
)
SELECT TotalSold FROM SalesData

but SSMS gives me this error:

Msg 208, Level 16, State 1, Line 1 Invalid object name

I'm using Avdentureworks Database sample.

enter image description here

any idea why is this happening? while it's been executed well in the demo video that I'm watching.

Upvotes: 3

Views: 27021

Answers (2)

C. Dennis Mosley
C. Dennis Mosley

Reputation: 1

You're missing few commands.

Place the keyword GO on the line after "USE AdventureWorks".

Upvotes: 0

Hadi
Hadi

Reputation: 37313

Based on the Comments of @DeepakPawar the solution was to

put a semicolon before with like 

;with cte (...) as ....

Also as @Pரதீப் mentioned - referring to the following WITH common_table_expression (Transact-SQL) documentation

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

Upvotes: 1

Related Questions