Column aliases in case statement in order by

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Column aliases in case statement in order by

Author

Topic

thanvi
Starting Member

11 Posts

Posted - 2007-01-18 : 15:08:55

Hi All,

I have this query :

select col1, col2, col3, col4, col5,..... , (select col99 from tab2) as alias1 from tab1 where <condition>
order by
case @sortby
when 'col1' then col1,
when 'col2' then col2,
when 'col3' then col3,
when 'col99' then col99
end

when i execute the above query it gives me the following error message.

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'col99'.

Thanks in advance.

Thanvi.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-18 : 15:45:29

Col99 is out out scope, it only exists in the subquery ... You've aliases it as Alias1. You need to reference it that way.

- Jeff

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-01-18 : 16:13:38

If that doesn't work for some reason or your colNs are of mixed datatypes you may continue to have problems. Check out this article and the link to it's comments for a discussion about dynamic ordering:
http://www.sqlteam.com/item.asp?ItemID=2209
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=5942

Be One with the Optimizer
TG

thanvi
Starting Member

11 Posts

Posted - 2007-01-18 : 17:11:03

Thanks for your help..

Let me rephrase the query.....

select col1, col2, col3, col4, col5,..... , (select col99 from tab2) as alias1 from tab1 where <condition>
order by
case @sortby
when 'col1' then col1,
when 'col2' then col2,
when 'col3' then col3,
when 'col99' then alias1
end

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'alias1'.

I typed col99 instead of alias1.

Hi TG,

Thanks for the links. I had gone through them and I didnt find anything related to using the column alias in the order by clause of case statement.

Thansk in advance.

Thanvi

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-18 : 19:40:47

select * from
(select col1, col2, col3, col4, col5,..... , (select col99 from tab2) as alias1 from tab1 where <condition>
) a
order by
case @sortby
when 'col1' then col1,
when 'col2' then col2,
when 'col3' then col3,
when 'col99' then alias1
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 01:15:37

Or change Alias1 (col99) to it's ordinal number in the ORDER BY.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources