January 17, 2011 at 3:10 pm
After running the SQL 2008 Upgrade Advisor over our SQL 2005 database (mode 90), a number of sp's were reported with the message
column aliases in the ORDER BY clause cannot be prefixed by the table alias
Taking a look at the code, we have a number of SP's that contain the following structure
select
...
,case
when rt.DateReceived = '1 jan 1900' then null
else rt.DateReceived
end as DateReceived
...
order by rt.DateReceived desc
Apart from repeating the case statement in the ORDER BY clause or changing the column alias, what method(s) can I implement to prevent this error?
-- John Oliver
Sometimes banging your head against a wall is the only solution.
January 17, 2011 at 9:09 pm
Solution:
What I didn't realise was the table alias name will take precedence over a column field name, so removing the table alias prefix from the ORDER BY is enough to order the results by the value of the alias (in this instance, the CASE statement).
,case
when rt.DateReceived = '1 jan 1900' then null
else rt.DateReceived
end as DateReceived
order by DateReceived
Performing some other checks, I have confirmed that when a query contains multiple tables with the same field name (Primary/Foreign key join), the ORDER BY will successfully use the alias (assuming the alias has the same name as the key fields).
-- John Oliver
Sometimes banging your head against a wall is the only solution.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply