CODEWITHSUNDEEP
sqlsql-servert-sqlcommon-table-expression
loyalflow
loyalflow

Reputation: 14929

Why is it saying RowId is an invalid column in my CTE query?

I have a CTE query that I scripted based on some online examples, not sure why I'm getting an error regarding the RowId:

DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = 120
SET @EndRow = 140

;WITH MyCTE AS
(
    SELECT 
        c1, c2, c3,
        ROW_NUMBER() OVER(ORDER BY gav.c1) AS RowId
    FROM 
        MyTable1 gav
)
SELECT *
FROM MyTable1 
WHERE RowId > @StartRow
  AND RowId < @EndRow
ORDER BY c1

I get this error:

Msg 207, Level 16, State 1, Line 15
Invalid column name 'RowId'.
Msg 207, Level 16, State 1, Line 16
Invalid column name 'RowId'.

In every example I have seen, they reference the RowId value from the CTE in the query, but it doesn't seem to be in scope for some reason?

Upvotes: 1

Views: 2649

Answers (1)

John Woo
John Woo

Reputation: 263943

the problem is you are using the name of the tablename when it should be the name of the CTE

DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = 120
SET @EndRow = 140

;WITH MyCTE AS
(
    SELECT c1, c2, c3,
    ROW_NUMBER() OVER(ORDER BY gav.c1) AS RowId
    FROM MyTable1 gav
)
SELECT *
FROM MyCTE     --------------- <<=== HERE
WHERE   RowId > @StartRow
        AND RowId < @EndRow
ORDER BY c1

Upvotes: 9

Related Questions