Oracle IN Statement

[Tank50]

HI Guys

These days I am checking whether amount is correctly upload into one of our table its located in oracle database below query used to check this
.
Select * from Amount_Paiad where CusID IN ('34343','34343','090094')

There is no issue in query.Problem is how many values I can enter to IN Statement.There is more than 5000records.

Problem is once I enter 5000 records into sql statement ,sql developer and sql navigator generate error message saying ' Only 1000 records can insert to IN statement.

I only have read access for database tables.Please let me is there more better way to do this,is there way to increase the values(More than 1000) used IN Statement.

Thanks
Tank50

[urtrivedi]

how u form the list of 5000 items, is it somewhere stored in same database. or you do it in your front end.

[debasisdas]

You need to pass the list of values as a subquery.

[Majestics]

http://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause

Check this its already solved here.

Select * from Amount_Paiad where CusID IN
(Select CusID From Customers_Tbl
 Where Cust_Stat = 12);

...or something similar.

[yash_792]

if you are able to create a temporary table of your 5000 records then you can either join these tables or you can pass these values into a sub-query for e.g.

select * from amount_paiad where cust_id in (select temp_field_name from temp_table)

Hope this is helpfull

[Prateek nandan]

HI Guys

These days I am checking whether amount is correctly upload into one of our table its located in oracle database below query used to check this
.
Select * from Amount_Paiad where CusID IN ('34343','34343','090094')

There is no issue in query.Problem is how many values I can enter to IN Statement.There is more than 5000records.

Problem is once I enter 5000 records into sql statement ,sql developer and sql navigator generate error message saying ' Only 1000 records can insert to IN statement.

I only have read access for database tables.Please let me is there more better way to do this,is there way to increase the values(More than 1000) used IN Statement.

Thanks
Tank50

if the record have id in serial no so you can use between(value1,value2)..