using case in where clause
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??
May 26th, 2012 2:48pm

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 tglich Blog Xing
Free Windows Admin Tool Kit Click here and download it now
May 26th, 2012 3:09pm

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.
May 26th, 2012 4:34pm

Hi There What error you are getting can you please give some more details? Many thanks Syed Qazafi Anjum
Free Windows Admin Tool Kit Click here and download it now
May 26th, 2012 4:40pm

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.
May 26th, 2012 5:06pm

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
Free Windows Admin Tool Kit Click here and download it now
May 26th, 2012 5:06pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics