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.
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
. . .