using case in where clause

Question

I have three conditions:yes, no,all with which my query changes values based on one attribute value.

I have created a parameter with these three values:@present with present,absent,both.

Considering my query should display different results when I use cases in where clause e.g.

case when @present='present' then condition is yes

else case when @present='absent' then condition is no

else case when @present='both' then condition in ('yes','no') end end end

It is not working the way should. Any Suggestions??

Answer 1

Hello,

Your CASE WHEN statement is wrong, you use "to often" the case command. Try it more this way:

case when @present='present' then 'YES'
     when @present='absent' then 'ABSENT'
     when @present='both' then 'BOTH' END AS Result

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

I am trying to use your code in where clause with my other conditions in following way but gives me error

I am not sure in this situation, using CASE is the solution or something else can be used.

and case when @present='present' then myattribute='YES'
     when @present='absent' then myattribute='ABSENT'
     when @present='both' then myattribute= 'BOTH' END

My need is that with this @present parameter where clause condition should change.But somewhere in code there is still an error.

Thanks for your help.

Answer 3

Hi There

What error you are getting can you please give some more details?

Many thanks

Syed Qazafi Anjum

Answer 4

Its resolved now..There was no need to use case in where clause.

and ((@present='present' and myattribute='Yes')
   or (@present='absent'  and myattribute='No')
 or  (@present='both' and myattribute= 'Both'  ))

This will do the magic.

Thanks

Answer 5

HI There

your wheer clause need to be like this not as you have posted above

WHERE
myattribute=case when @present='present' then 'Y'
     when @present='absent' then 'N'
     when @present='both' then  'BOTH'
     END

Many Thanks

Syed Qazafi Anjum

Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.