Answered by:
CASE statement in where Clause

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
- Marked as answer by Allen Li - MSFT Thursday, April 11, 2013 3:08 AM
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
- Marked as answer by Allen Li - MSFT Thursday, April 11, 2013 3:08 AM
Tuesday, April 2, 2013 4:11 PM -
thanks i was drawing a blacnkTuesday, 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