Answered by:
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??
Wednesday, May 23, 2012 6:41 PM
Answers
-
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'
ENDMany Thanks
Syed Qazafi Anjum
Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.
- Edited by Syed Qazafi Anjum Wednesday, May 23, 2012 9:01 PM
- Marked as answer by Lola Wang Thursday, May 24, 2012 6:24 AM
Wednesday, May 23, 2012 8:59 PM
All replies
-
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- Proposed as answer by Syed Qazafi Anjum Wednesday, May 23, 2012 8:10 PM
Wednesday, May 23, 2012 7:02 PM -
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' ENDMy 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.- Edited by somya12 Wednesday, May 23, 2012 8:28 PM
Wednesday, May 23, 2012 8:27 PM -
Hi There
What error you are getting can you please give some more details?
Many thanks
Syed Qazafi Anjum
Wednesday, May 23, 2012 8:33 PM -
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
Wednesday, May 23, 2012 8:59 PM -
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'
ENDMany Thanks
Syed Qazafi Anjum
Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.
- Edited by Syed Qazafi Anjum Wednesday, May 23, 2012 9:01 PM
- Marked as answer by Lola Wang Thursday, May 24, 2012 6:24 AM
Wednesday, May 23, 2012 8:59 PM