locked
CASE statement in where Clause RRS feed

  • Question

  • Hi i am trying to use a CASE STATEMENT in my Where Clause to populate one of the columns in the table i'm creating with a specific name  but keep getting an can i please get some help?

    i get error    

    Msg 102, Level 15, State 1, Line 63
    Incorrect syntax near '='. (this is the = in the case statement)

    SELECT*

    FROM MyTable

    Where
    Mytable.myvalue = 7
    AND mytable.someDate >= (DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0))
    AND (CASE WHEN mytable.myvalue= 145 THEN name = 'Online' ELSE END)

    Tuesday, April 2, 2013 4:01 PM

Answers

  • AND name= CASE WHEN myvalue= 145 THEN  'Online' ELSE 'someothervalue' END
    Tuesday, April 2, 2013 4:11 PM
  • You have a value Mytable.myvalue = 7 and in the CASE expression the CASE WHEN myvalue= 145  will be false and the name should always match with 'someothervalue'. Are you able to retrieve anything with below query ?

    SELECT * 
    FROM MyTable
    WHERE
    Mytable.myvalue = 7
     AND mytable.someDate >= (DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0))
     AND name='someothervalue'


    Narsimha

    • Proposed as answer by Naomi N Tuesday, April 2, 2013 5:10 PM
    • Marked as answer by Allen Li - MSFT Thursday, April 11, 2013 3:08 AM
    Tuesday, April 2, 2013 5:02 PM

All replies

  • AND name= CASE WHEN myvalue= 145 THEN  'Online' ELSE 'someothervalue' END
    Tuesday, April 2, 2013 4:11 PM
  • thanks i was drawing a blacnk
    Tuesday, April 2, 2013 4:13 PM
  • Can you provide your table DDL, some sample data and your expected result? Thanks.
    Tuesday, April 2, 2013 4:17 PM
  • Your Case statement will never work.   In your case, Case statement returns a string and you are comparing with other boolean reasults within the WHERE clause. I believe your case statement should be after SELECT clause.

    Regards, RSingh

    Tuesday, April 2, 2013 4:19 PM
  • You have a value Mytable.myvalue = 7 and in the CASE expression the CASE WHEN myvalue= 145  will be false and the name should always match with 'someothervalue'. Are you able to retrieve anything with below query ?

    SELECT * 
    FROM MyTable
    WHERE
    Mytable.myvalue = 7
     AND mytable.someDate >= (DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0))
     AND name='someothervalue'


    Narsimha

    • Proposed as answer by Naomi N Tuesday, April 2, 2013 5:10 PM
    • Marked as answer by Allen Li - MSFT Thursday, April 11, 2013 3:08 AM
    Tuesday, April 2, 2013 5:02 PM