Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

Conditional where clause

Posted on 2009-05-05
4
Medium Priority
?
2,321 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
[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 Comments
 
LVL 74

Accepted Solution

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

647 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