SQL IN statement to extract for 100k records

Question

I have a file SRQ which is having 10000 SRQ_ID which are unique. I have one table(TABLE1) which is having 2 columns namely SRQ_ID,WORK_ID . I needs to write a query which will search the table(TABLE1) for all the SRQ_ID's in the file SRQ and will display the output with corresponding WORK_ID.

I tried the below code. But IN clause is only applicable for 1000 records. How to run the same if I have 100k records?

select WO_ID 
from TABLE1 
where SRQ_ID in ('B6512DF0','5838FABC','EC5D804C','074DD65C')

@deadlock: I don't see any indication that this is for Postgres. And judging by the "*applicable for 1000 records*" I actually think it's Oracle, because Oracle only allows for 1000 literal values in the `IN` operator — , Dec 06 '13 at 13:05
Without RDBMS it is unclear what you'Re asking. With RDBMS it is duplicate. Just use a search engine to find out answer and delete this question. This is duplicated 100s of times. — usr, Dec 06 '13 at 13:07
@StingyJack it's originally an Oracle issue. I thought it's a PostgreSQL too. But it seems not! — dlock, Dec 06 '13 at 13:17
i got the answer using union all approach.. it worked fine for me..thanks a lot to all .. — user2642751, Dec 06 '13 at 13:35

score 0 · Answer 1 · answered Dec 06 '13 at 13:41

0

Is there a reason you can't just do a join between the tables using SRQ_ID?

select wo_id from table1 join srq using srq_id

This will give you the work id for all rows that have a srq_id value in the srq table.

answered Dec 06 '13 at 13:41

Mark Wagoner

1,729
1
13
20

We supposed to check on prod envirionment that is why i was not willing to take that option... – user2642751 Dec 06 '13 at 15:26

score 0 · Answer 2 · answered Dec 06 '13 at 13:59

0

If the file s on the database server then you could access it as an external table, and join the two. Otherwise, I'd suggest bulk loading the codes into a global temporary table and performing the join against that.

answered Dec 06 '13 at 13:59

David Aldridge

51,479
8
68
96

Wernfried Domscheit · Answer 3 · 2013-12-06T16:25:22.953

In case you don't like to create a temporary table, you can use a nested table:

CREATE OR REPLACE TYPE VARCHAR_TABLE_TYPE AS TABLE OF VARCHAR2(20);

select WO_ID 
from TABLE1 
where SRQ_ID MEMBER OF VARCHAR_TABLE_TYPE('B6512DF0','5838FABC','EC5D804C','074DD65C');

But I don't know the limit for initializing a nested table. Oracle documentation says: "Because a nested table does not have a declared size, you can put as many elements in the constructor as necessary."

SQL IN statement to extract for 100k records

3 Answers3