Solved

Conditional where clause

Posted on 2009-05-05
4
2,313 Views
Last Modified: 2012-06-27
Hello,

I'm having a problem where I have a WHERE-clause which depends on input from the user.

My first thought was something similar to the switch-statement you find in some languages but I can't seem to find anything similar.

A CASE is not acceptable since that returns a value and I need to set conditions. And I cannot use stored procedures or similar either; it all has to be done in a SELECT.

Anyone got any ideas? See pseudo-code below.
SELECT

	something

FROM

	somewhere

WHERE

	switch(input)  {

		case 100:

			field1 = input

			break;

		case 101:

			field1 = input OR field1 = 200

			AND

			field2 IN (1,2,3)

			break;

		case 102:

			field1 = input

			AND

			field2 IN (2,5,7)

			break;

		case 103:

			field1 = input

			break;

	}

Open in new window

0
Comment
Question by:Elisas
4 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 24303577
yes, use case like this...


SELECT something
  FROM somewhere
 WHERE CASE
           WHEN input = 100 AND field1 = input THEN 1
           WHEN input = 101 AND (field1 = input OR field1 = 200 AND field2 IN (1, 2, 3)) THEN 1
           WHEN input = 102 AND field1 = input AND field2 IN (2, 5, 7) THEN 1
           WHEN input = 103 AND field1 = input THEN 1
           ELSE 0
       END = 1
0
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 24308861
Hi,

Your switch statement is just a OR condition:

     where  ( input = 100 AND field1 = input )
     or         ( input = 101 AND (field1 = input OR field1 = 200 AND field2 IN (1, 2, 3)) )
     or         ( input = 102 AND field1 = input AND field2 IN (2, 5, 7) )
     or         ( input = 103 AND field1 = input )

Regards,
Franck.
0
 

Author Closing Comment

by:Elisas
ID: 31577964
Excellent. I was stumbling a bit along those lines but I guess I didn't manage to wrap my mind properly around it then. Much obliged.
0
 
LVL 6

Expert Comment

by:bokist
ID: 24311864
One of possible solutions :

select * from some_table
 where field1 = case when switch in (100, 103) then input end
        or (switch = 101 and (field1 = input or field1 = 200) and (field2 in (1,2,3)))  
        or (switch = 102 and (field1 = input and field2 in (2,5,7)))  

See attached sample.

Sample.txt
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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. …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

863 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now