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