locked
using case in where clause RRS feed

  • 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'
         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.


    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

    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' 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.
    • 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'
         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.


    Wednesday, May 23, 2012 8:59 PM