Using tuples in ORACLE IN clause and a condition for one element in the tuple

Question

I have seen many questions here for using tuples in the IN clause. My situation is a little different from the others. General usage of tuples in IN clause will look as below

    Select * from MY_TABLE
    where (id,name,date) IN ((1,'new','10-JUL-13'),(2, 'old','09-JUN-13'))

Considering the above query, my requirement is to retrieve the records with id and name values along with date in a particular range. lets say

    effectiveDate <= date <= termDate

I'm using ORACLE database and MyBatis ORM. I'll get data as a list of objects, so when I use mybatis I can use a foreach/for loop to populate the tuples, but when I want to use condition for one of those values from the object.

When I use Mybatis for one value read from a list, the where clause as below

    <where>
        and (id,name) IN
   <foreach item="object" collection="data" open="(" separator=","close=")">
    (#{object.id},#{object.name})
   </foreach>
    </where>

I have to include the condition in the loop as well.

Waiting for the expert advice. Thanks in advance.

`and date between '1/2/2013' and '2/3/2013'` won't work as a final predicate? Good luck. — shellter, Jul 17 '13 at 01:23

Gordon Linoff · Accepted Answer · 2013-07-17T01:32:54.180

10

Are you looking for something like this?

select *
from MY_TABLE
where (id, name) in ((1,'new'), (2, 'old')) and
      date between effectiveDate and termDate

This looks for the pairs in a list and then checks for the dates between a range of dates.

EDIT:

I think you want to break this into multiple clauses, one for each set of values:

where (id = 1 and name = 'new' and date between eff1 and term1) or
      (id = 2 and name = 'old' and date between eff2 and term2) or
      . . .

edited Jul 17 '13 at 01:32

answered Jul 17 '13 at 01:23

Gordon Linoff

1,242,037
58
646
786

Not exactly.. I have edited the post.. Please go through once if you can. Thanks for the resposne though. – Bharath ABK Jul 17 '13 at 01:31
Is there any limit on how many tuples/conditions I can use in the above scenario?? – Bharath ABK Jul 17 '13 at 15:59
@Neal . . . The limit is on the size of the SQL statement, so you should be able to fit at least hundreds if not thousands. However, at some point, you might think about putting the values in a table and using `join` for the filtering. – Gordon Linoff Jul 17 '13 at 16:09

score 2 · Answer 2 · answered Jul 17 '13 at 19:46

The above solution for my question is rewritten below for Mybatis users

    <where>
    <foreach item="object" collection="data" separator="OR">
      (id = #{object.id} AND name = #{object.name} AND (effDt < #{object.date} < termDt))
    </foreach>
    </where>

There won't be a big performance difference when you use either IN or OR to separate the data/conditions in the WHERE clause.

    FYI : *http://stackoverflow.com/questions/3074713/in-vs-or-in-the-sql-where-clause*

Using tuples in ORACLE IN clause and a condition for one element in the tuple

2 Answers2

Linked