Help with CASE - SQL Server Forums

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)

Help with CASE

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-20 : 01:12:23

Hello.Easy i guess but...
I'm trying the above but it does not compile.I want to have a value to each "else", not only the final.


SET @tempc =
Case 
When @value1 = 0 and @value2 > 1 then 1 else 0
When @value1 = 1 and @value2 > 5 then 1 else 0
When @value1 = 3 and @value2 > 6 then 1 else 0
Else 3
End

I get "Incorrect syntax near the keyword 'When'."
If i remove the "else 0" then it compiles but i want to control then other else's also.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-20 : 01:13:56

it should be


SET @tempc =
Case 
When (@value1 = 0 and @value2 > 1) 
or (@value1 = 1 and @value2 > 5) 
or (@value1 = 3 and @value2 > 6) then 1 
Else 3
End

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

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-20 : 01:17:28

Hi.
But this way i don't get the zero value.
then 1 else 0

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-20 : 01:24:41

And i want to do this cuz maybe i can change the values and do:

SET @tempc =
Case 
When @value1 = 0 and @value2 > 1 then 1 else 0
When @value1 = 1 and @value2 > 5 then 1 else 1
When @value1 = 3 and @value2 > 6 then 1 else 2
Else 3
End

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-20 : 01:41:48

you cant have more than one else within case. please state what your exact requirements are

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

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-20 : 02:02:03

Hi.Yes i guess i was trying it wrong.
I want to know where the comparison were, and failed.
Something like if @value1 =3 then even if the comparison (@value2 > 6) fails, i want to return value = 2 and not the final else (value = 3).
Something like (in vb)

If value1=3 then
if value2 > 6 return 1 else return 2

If value1=0 then
if value2 > 2 return 1 else return 0
.
.
.
else return 3.

Must i if's SQL then?
I could also do a nested case in VB but i don't know how in SQL...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-20 : 03:11:25

SET @tempc =
Case
When @value1 = 0 then case when @value2 > 1 then 1 else 0 end
When @value1 = 1 then case when @value2 > 5 then 1 else 1 end
When @value1 = 3 then case when @value2 > 6 then 1 else 2 end
Else 3
End

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

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-20 : 03:12:57

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-20 : 03:22:07

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources