Oracle multiple fields in Select IN Parameter

Question

Select * FROM STUDENT 
WHERE (student.course, student.major) IN (SELECT schedule.course, schedule.major 
FROM schedule)

What if i have to provide static values, what would be the query like? Because I am passing the SQL from a middle layer based on input parameters.

Edit: I am looking to search based on multiple sets of values. For ex.

Select * FROM STUDENT 
WHERE (student.course, student.major) IN 
(('MBA', 'Computers'), ('BA', 'Computers'))

What static values, exactly, are you trying to handle? Are you trying to send multiple values for a single column (i.e. an array of majors)? Or are you trying to send static values for multiple columns (i.e. a course and a major)? — Justin Cave
– Justin Cave, Commented Aug 23, 2011 at 20:54
You are looking to replace (SELECT schedule.course, schedule.major FROM schedule) with a static/parameter list? — Cade Roux
– Cade Roux, Commented Aug 23, 2011 at 20:54
Cade, yes, I am looking to replace with the static parameter list (pass 1 or more sets of data). — priceline
– priceline, Commented Aug 23, 2011 at 21:45
@priceline - OK. And what is the "middle layer" that you are passing the data from? — Justin Cave
– Justin Cave, Commented Aug 24, 2011 at 4:17
Your edit appears to be a query that should work just fine. Have you tried it? — Jeffrey Kemp
– Jeffrey Kemp, Commented Mar 15, 2012 at 10:53

Henry Collingridge · Accepted Answer · 2012-03-15 03:17:37Z

1

I'm pretty sure you can use this:

Select * FROM STUDENT 
WHERE (student.course, student.major) 
IN (SELECT 'MBA', 'Computers' from DUAL 
    union SELECT 'BA', 'Computers' from DUAL);

:D

answered Mar 15, 2012 at 3:17

1,9702 gold badges14 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

beny23 · Accepted Answer · 2011-08-23 21:08:11Z

0

Do you mean (or am I misunderstanding something about hardcoding?):

select * from student
 where course = 'DB101'
   and major = 'MyMajor'

answered Aug 23, 2011 at 21:08

beny23

35.1k6 gold badges84 silver badges86 bronze badges