I have a table with a primary key consisting of two columns. I want to retrieve a set of rows based on two input arrays, each corresponding to one primary key column.
select pkt1.id, pkt1.id2, ... from PrimaryKeyTable pkt1, table(:1) t1, table(:2) t2
where pkt1.id = t1.column_value and pkt1.id2 = t2.column_value
I then bind the values with two int[] in odp.net.
This returns all different combinations of my resulting rows. So if I am expecting 13 rows I receive 169 rows (13*13). The problem is that each value in t1 and t2 should be linked. Value t1[4] should be used with t2[4] and not all the different values in t2.
Using distinct solves my problem, but I'm wondering if my approach is wrong. Anyone have any pointers on how to solve this the best way? One way might be to use a for-loop accessing each index in t1 and t2 sequentially, but I wonder what will be more efficient.
Edit: actually distinct won't solve my problem, it just did it based on my input-values (all values in t2 = 0)
This question is solved now. Here is the answer if you're interested.
select id1, id2 from t, (select rownum rn1, a1.* from table(:1) a1) a1, (select rownum rn2, a2.* from table(:2) t2) t2 where (id1, id2) in ((a1.column_value, a2.column_value)) and rn1 = rn2
http://forums.oracle.com/forums/thread.jspa?threadID=1083982&tstart=15