Solved

Complex conditional WHERE statement with CASE

Posted on 2008-09-29
5
208 Views
Last Modified: 2012-05-05
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
Comment
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
  • Learn & ask questions
  • 4
5 Comments
 
LVL 3

Expert Comment

by:R_Janssen
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

by:sdstuber
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

by:sdstuber
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

by:
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

by:sdstuber
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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question