alternative to the 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

SQL Server 2005 Forums

Transact-SQL (2005)

alternative to the Case Statement

Author

Topic

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2011-12-23 : 01:21:46

Hi,

I have one scenario where i use following query
select number
case WHEN 1 Then 'good'
when 2 then 'avg'
when 3 then 'poor'
end as performance from test.

is there any alternative to above query where we can eleminate case statement.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-23 : 01:34:23

might be. . . but what's wrong with that query ?

KH
[spoiler]Time is always against us[/spoiler]

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2011-12-23 : 01:57:00

Nothing wrong but i am trying to write query without case statement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-23 : 01:57:05

you can eliminate case by putting a mapping table with two fields like (perfid,perfmeasure)
and you can put values inside it

then above CASE statement can be dispensed with and instead you'll add a join to table created as


INNER JOIN MappingTable mt
ON mt.perfid = number
...

and in your select include mt.perfmeasure instead of CASE..WHEN

by implementing it as above its scalable also as for for each new measure value addition does not require you to modify the procedure but just need to add a new insert statement to add the value to table

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

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2011-12-23 : 02:04:16

you menat to say, i should create another table and add the value like performance,id..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-23 : 03:13:17

yep...if you want to make solution scalable and avoid putting a series of case expressions. once expression number goes beyond a limit table approach might work faster if you've proper indexes on table created

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

Sachin.Nand

2937 Posts

Posted - 2011-12-23 : 05:04:58

quote:
Originally posted by yadhu_cse

Hi,

I have one scenario where i use following query
select number
case WHEN 1 Then 'good'
when 2 then 'avg'
when 3 then 'poor'
end as performance from test.

is there any alternative to above query where we can eleminate case statement.

IIF....but for that you have to wait for SQL Server 2012 release.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources