How to use 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)

How to use CASE

Author

Topic

goodsolution
Starting Member

38 Posts

Posted - 2009-10-01 : 17:33:00

Hi all,
How to use CASE statement inside the select statement to evoluate the all column values at a time.

Ex:

select top 10
RE.response_id,
RE.survey_id,
RA.attribute_name,
RAR.Response_attribute_value,
RE.source,

(case
when re.survey_id = 191 and source = 0 then 11191
when re.survey_id = 32 and source = 0 then 1132
when (RA.attribute_name = 'FILENAME' and RE.source = 0 and RAR.Response_attribute_value = 'UNKNOWN' )then '-99'
end ) survey_id_test from response RE with (nolock) join
responseattribute RA with (nolock)
on Re.Survey_id = Ra.survey_id
join responseattributeresult RAR with (nolock)
on RAR.Response_attribute_id = RA.response_attribute_id

The above code is working fine if i want to evoluvate Survey_id or Attribute_name individually, but it is not working as together.

Can you give any advice

Thanks.

mivey4
Yak Posting Veteran

66 Posts

Posted - 2009-10-01 : 19:04:41

Can you provide an example of what you mean when you say it is not working as together?

I'm not certain that I understand the problem.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 07:20:11

case is an expression not a statement. as i understand you're asking a way to manipulate multiple columns values using same case. if it is so, this is not possible as case is used only to evaluate value of a single field conditionally.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-10-02 : 07:36:53

quote:
if it is so, this is not possible as case is used only to evaluate value of a single field conditionally

That's not correct, you can use multiple AND/OR conditions in CASE evaluations, or even nested CASEs. You cannot return more than one value with one CASE expression, but you can use multiple CASEs to do that.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 07:42:17

quote:
Originally posted by robvolk

quote:
if it is so, this is not possible as case is used only to evaluate value of a single field conditionally
That's not correct, you can use multiple AND/OR conditions in CASE evaluations, or even nested CASEs. You cannot return more than one value with one CASE expression, but you can use multiple CASEs to do that.

I also meant the same point. sorry if i was not clear

as i understand you're asking a way to manipulate multiple columns values using same case. if it is so, this is not possible as case is used only to evaluate value of a single field conditionally.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources