Invalid column name error

  • Guras

    SSCertifiable

    Points: 6513

    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.

  • doug.brown

    SSCertifiable

    Points: 5528

    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]
  • Sue_H

    SSC Guru

    Points: 90287

    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 3 (of 3 total)

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