Reputation: 14929
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
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