Friday, 16 August 2013

"Invalid Column Name" error in query with order by case statement and outer select

"Invalid Column Name" error in query with order by case statement and
outer select

I am trying to order by a column in an outer select statement using a case
statement to determine the fields to order by. For some reason, I cannot
use the column created in the outer select. If I try to order by that
column without using a case statement then it works. What am I doing
wrong?
Pseudocode to illustrate problem:
DECLARE @orderBy varchar(8)
SET @orderBy = 'rating'
SELECT
CASE
WHEN rating is not null THEN 1
ELSE 0
END as hasRating,
*
FROM
(SELECT col1, ...) AS table1
LEFT OUTER JOIN
(SELECT col1, rating, ...) AS table2
ON table1.col1 = table2.col2
--causes error "Invalid column name 'hasRating'"
ORDER BY
CASE WHEN @orderBy = 'rating' THEN hasRating END DESC,
CASE WHEN @orderby = 'something else' THEN ...
--works
ORDER BY hasRating desc

No comments:

Post a Comment