Column Aliases In ORDER BY Claus Cannot Be Prefixed By Table Alias – SQL 2008 R2

IT Support Forum Forums Databases Microsoft SQL Server 2008 R2 SQL Queries Column Aliases In ORDER BY Claus Cannot Be Prefixed By Table Alias – SQL 2008 R2

This topic contains 0 replies, has 1 voice, and was last updated by  Webmaster 3 years, 1 month ago.

  • Author
    Posts
  • #492

    Webmaster
    Keymaster

    When running the SQL Upgrade Advisor on a database I’m migrating from SQL Server 2005 to Server 2008 R2, I get this error relating to a stored procedure:

    Column aliases in ORDER BY clause cannot be prefixed by table alias

    This means that your stored procedure has an alias used in a SELECT statement to identify a column, then later that alias is used in an ORDER BY clause. SQL Server 2005 allows this, but SQL Server 2008 R2 does not. Here’s an example, where the alias is used in an ORDER BY clause:

    SELECT FirstColumn AS c1, SecondColumn AS c2
    FROM db1.table1 t1
    ORDER BY t1.c1

    In the example, Server 2008 R2 will error because you aren’t allowed to use the column alias c1 in an ORDER BY clause, as the Upgrade Advisory message suggests.

    To fix the error, remove the column aliases in the ORDER BY clause and replace them with the column name, as in the example below:

    SELECT FirstColumn AS c1, SecondColumn AS c2
    FROM db1.table1 t1
    ORDER BY t1.FirstColumn

    An alternative fix is to not prefix the table alias, as in the example below:

    SELECT FirstColumn AS c1, SecondColumn AS c2
    FROM db1.table1 t1
    ORDER BY c1

You must be logged in to reply to this topic.