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, Aug 23 '11 at 20:54
You are looking to replace `(SELECT schedule.course, schedule.major FROM schedule)` with a static/parameter list? — Cade Roux, Aug 23 '11 at 20:54
Cade, yes, I am looking to replace with the static parameter list (pass 1 or more sets of data). — priceline, Aug 23 '11 at 21:45
@priceline - OK. And what is the "middle layer" that you are passing the data from? — Justin Cave, Aug 24 '11 at 04:17
Your edit appears to be a query that should work just fine. Have you tried it? — Jeffrey Kemp, Mar 15 '12 at 10:53

score 1 · Answer 1 · answered Mar 15 '12 at 03:17

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 '12 at 03:17

score 0 · Answer 2 · answered Aug 23 '11 at 21:08

0

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

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

answered Aug 23 '11 at 21:08

beny23

2 Answers2