Using Case statment - 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

General SQL Server Forums

New to SQL Server Programming

Using Case statment

Author

Topic

ann06
Posting Yak Master

171 Posts

Posted - 2008-04-07 : 07:42:51

Hi There i have a query that uses conditional Select statment my query is:
Select A,B,C case
when '1' then 'one'
when '2' then 'two'
end
from table

what if i want to include addional condition in the case condition like C case
when '1' and A is null then 'C is one and A is null'

Thank you :D

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-07 : 07:48:13

I am not sure what you are trying to say. Is this what you want?

Select A,B,C,
Case
when C = '1' and A is null then 'C is one and A is null'
when C = '2'...
End
from table

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-07 : 07:48:58

case satatements are processed in order and your case statement is invalid - doesn't specify the expression to test

Select A,B,C case
when A = '1' then 'one'
when B = '2' then 'two'
when C = '1' and A is null then 'three'
end
from table

or same thing
Select A,B,C case
when A = '1' then 'one'
when B = '2' then 'two'
when C = '1' then case when A is null then 'three' end
end
from table

The other way of presenting a case statement when there is a single expression to evaluate is
Select A,B,C case A
when '1' then 'one'
when '2' then 'two'
end
from table

Note - I try to always include an else clause rather than let it default to null - I think it's more readable.

==========================================
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 - 2008-04-07 : 07:49:09

SELECT A, B, C,
CASE
WHEN C = 1 AND A IS NULL THEN 'C is one and A is null'
WHEN B = 2 AND A > C TEHN 'B is two and A is greater than C'
ELSE 'Nothing to tell, really
END
FROM Table

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources