column alias and case statement

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 alias and case statement

Author

Topic

boehnc
Starting Member

15 Posts

Posted - 2012-02-20 : 13:40:01

I understand the SQL evaluation process..but trying to figure a workaround. Any help is appreciated. Thanks.

Select Patienttype, DivisionCode, Substring(AnestheticRC,1,1) as AnesthRisk2,

case when (AnesthRisk2 > 2) then 1, else 0
end as Anesthrisk

from Table1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-20 : 13:41:31

sorry didnt understdn what the issue is. can you explain?
as of now your CASE statement looks good except for unwanted , in between..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

boehnc
Starting Member

15 Posts

Posted - 2012-02-20 : 13:48:25

ah yes..sorry...it's coming back as Invalid column name 'AnesthRisk2'.

I assumed the SQL processing method had a problem with recognizing the alias column in the Select statement. This is the only part of my query that has "AnesthRisk2" column name.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-20 : 14:18:15

ok..reason is AnesthRisk2 is an alias given inline in same select so you can use it directly in case when. if you want to use it like that you need to first form a derived table with alias and use case over it. like below:


select *, 
case when (AnesthRisk2 > 2) then 1, else 0
end as Anesthrisk

from
(
Select Patienttype, DivisionCode, Substring(AnestheticRC,1,1) as AnesthRisk2
from Table1
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources