CODEWITHSUNDEEP
sqlsql-server
hattybse
hattybse

Reputation: 43

Invalid column name error when column in place

I am trying to select rows where the end date is within a range. In my dbo.subscription table I have startdate and enddate columns. When I use startdate, the query runs, but when I use enddate, it says the column name is invalid.

I have tried using '' or "" [] around enddate, I have also tried writing the full name of the column out (dbo.subscription.enddate) but it does not seem to make a difference.

;WITH cte 
     AS (SELECT Membershipnumber as Id
               Row_number() 
                  OVER ( 
                    partition BY membershipnumber 
                    ORDER BY subscription.enddate DESC) AS rownumber 
         FROM   [dbo].[userprofile] 
                INNER JOIN dbo.subscription 
                        ON userprofile.id = subscription.userprofileid 
                INNER JOIN dbo.subscriptiontype 
                        ON subscriptiontype.id = subscription.subscriptiontypeid

        ) 
SELECT * 
FROM   cte 
WHERE  rownumber = 1 and enddate between'2014-12-31' and '2018-12-31'
order by Id

Error message:

Msg 207, Level 16, State 1, Line 21
Invalid column name 'enddate'.
Msg 207, Level 16, State 1, Line 21
Invalid column name 'enddate'.

Upvotes: 0

Views: 2272

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

Your final SELECT is a SELECT FROM cte.

And cte only defines two columns: Id and RowNumber.

enddate is not defined in cte.

Upvotes: 5

Related Questions