Invalid column name error

  • declare @DTBEGIN date = '02/01/2018'

    declare @DTEND date = '05/22/2019'

    SELECT * FROM (

    SELECT c.col1,c.col2

    ,ROW_NUMBER() OVER (

    PARTITION BY [c.col2,]

    ORDER BY [c.col3] DESC

    ) AS [ROW NUMBER]

    FROM [table1] c

    inner join table2 wrk

    on c.col2 = wrk.col2

    where CAST(CONVERT(VARCHAR,C.col4,101) AS DATETIME) BETWEEN @DTBEGIN AND @DTEND

    ) AllRecs

    WHERE Allrecs.[ROW NUMBER] = 1

    ORDER BY Allrecs.[ROW NUMBER] DESC

     

    --I am getting error

    Msg 207, Level 16, State 1, Line 6

    Invalid column name 'c.col2.

    Msg 207, Level 16, State 1, Line 7

    Invalid column name 'c.col3'.

     

    The table has these columns.

  • Part of the problem may be there is an errant comma in your Partition:

    PARTITION BY [c.col2,]

    Should be (if you only want to partition on the one field):

    PARTITION BY [c.col2]

    Or perhaps something like:

    PARTITION BY [c.col2,c.col1]
  • In addition to removing the comma after the partition by column, you can't use an alias in the select...over clause. You'd want to split the row_number out with a CTE or redo the derived table, join. I can't say how to redo it as it looks like something you used an example and redoing what you posted is as simple as removing the join to table2 since it's not used and just get rid of the alias. This part of the documentation explains not using aliases:

    SELECT - OVER Clause (Transact-SQL)

    Sue

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply