0

I am getting the error

ORA-01795 maximum number of expressions in a list is 1000

for the below query

select emp_id, emp_name, emp_code
from emp e
where emp_id in (001,002,005,006....18000);

I need to put 18000 records in the IN clause. How can I achieve this? How can I modify my given query?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Varun
  • 21
  • 1
  • 2
  • 1
    Put the 18000 records in a table and join with that. – jpw Aug 06 '16 at 17:47
  • You are completely abusing the `IN` clause. And if you really only want to check all values between 1 and 18000, then you can express it with `where emp_id between 1 and 18000`. Isn't that good enough for you? What are you trying to do? – sstan Aug 06 '16 at 18:13
  • Have none of the answers from [all the previous questions](http://stackoverflow.com/search?tab=votes&q=ora-01795) helped you? if not, why? Where are the values coming from, and where are you running the query from? (I'd probably use a table collection with member-of if you have an external list, e.g. in a Java array, but it depends..) – Alex Poole Aug 07 '16 at 08:12
  • Possible duplicate of [Is there a workaround for ORA-01795: maximum number of expressions in a list is 1000 error?](http://stackoverflow.com/questions/17842453/is-there-a-workaround-for-ora-01795-maximum-number-of-expressions-in-a-list-is) – Gabriel Mongeon Feb 22 '17 at 11:33

1 Answers1

0

You can (and almost surely should) put the "records" (the values) in a table. Then you can do a join as @jpw suggested in a Comment; an IN condition is treated by the query engine as a join anyway.

Or, if you want to use the IN syntax, you still need the values in a table, and rewrite the condition as

... where emp_id in ( select id from this_table )

where "this_table" is the name of this new table, and "id" is the column name. The 1000 expression limit applies to expressions that you hard-code in the IN list, it does not apply to the number of results returned by a subquery.