Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
theresamarieb
Starting Member
2 Posts |
 Posted - 2004-02-16 : 17:20:15
|
| Hi,I am trying to use a CASE statement in a WHERE clause and am having trouble.The syntax looks like this:WHERE FieldName_1 IN (CASE WHEN FieldName_2 = 'Value' THEN ('FieldName_1_Value_1') ELSE ('FieldName_1_Value_2', 'FieldName_1_Value_3', 'FieldName_1_Value_4' END)The error says incorrect syntax on the ',' in the CASE statement (references a line number). Can you not use a CASE statement following the IN operator? I'm assuming the comma is being read as if I am trying to use it in the CASE statement instead of inside the parentheses. Any ideas? |
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2004-02-16 : 18:06:40
|
| You can indeed use CASE inside IN, but the true or false part of the CASE expression can return only one value. For example:CREATE TABLE #t1(i int, j int)INSERT #t1(i, j) VALUES (1, 1)INSERT #t1(i, j) VALUES (1, 2)INSERT #t1(i, j) VALUES (2, 1)INSERT #t1(i, j) VALUES (2, 2)SELECT * FROM #t1 WHERE j in (CASE WHEN i%2=0 THEN 2 ELSE 1 END)You might want to rewrite your query as shown below:WHERE (FieldName_2 = 'Value' AND FieldName_1 = 'FieldName_1_Value_1')OR FieldName_2 IN ('FieldName_1_Value_2', 'FieldName_1_Value_3', 'FieldName_1_Value_4')--HTH,Vyashttp://vyaskn.tripod.com |
 |
|
|
|
|
|
|
|
CASE Statement