Solved

# Complex conditional WHERE statement with CASE

Posted on 2008-09-29
207 Views
The query I'm trying to build is almost complete. The query is something like this:

SELECT *
FROM table
WHERE condition1
AND field3 IN (CASE field2='ABC' THEN 'textvalue1', 'textvalue2', 'textvalue3' ELSE field3 END)

In normal language: If field2 contains the value 'ABC', then I want field3 to match any of three string values. If field2 doesn't contain the value 'ABC', field3 can be anything.

The following works (but is of no use to me):

SELECT *
FROM table
WHERE condition1
AND field3 IN (CASE field2='ABC' THEN 'textvalue1'' ELSE field3 END)
0
Question by:VSdB
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4

LVL 3

Expert Comment

ID: 22597090
Try encapsulating?
(CASE field2='ABC' THEN ('textvalue1', 'textvalue2', 'textvalue3') ELSE field3 END)?

No management studio at hand so can't test if this works. CASE's in SQL are limited so there is a chance you'll have to write it out a bit more detailed. aka. More if's and multiple select statements based on the information at that particular step.

0

LVL 74

Expert Comment

ID: 22597119
SELECT *
FROM table
WHERE condition1
AND (field3 = (CASE field2='ABC' THEN 'textvalue1' ELSE field3 END)
or field3 = (CASE field2='ABC' THEN 'textvalue2' ELSE field3 END)
or field3 = (CASE field2='ABC' THEN 'textvalue3' ELSE field3 END)
)

0

LVL 74

Expert Comment

ID: 22597127
or

SELECT *
FROM table
WHERE condition1
AND field3 in ( (CASE field2='ABC' THEN 'textvalue1' ELSE field3 END),
(CASE field2='ABC' THEN 'textvalue2' ELSE field3 END),
(CASE field2='ABC' THEN 'textvalue3' ELSE field3 END)
)

0

LVL 74

Accepted Solution

sdstuber earned 250 total points
ID: 22597144
or

SELECT *
FROM table
WHERE condition1
AND (field2 != 'ABC'
or (field2='ABC' and  field3 in ('textvalue1', 'textvalue2', 'textvalue3' ))
)

0

LVL 74

Expert Comment

ID: 22597256
Why only a B?  What else did you need?  If the answer was insufficient, please ask for further clarifications rather than assign a penalty grade
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Simple Linear Regression
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
###### Suggested Courses
Course of the Month1 day, 19 hours left to enroll