Where in more than 1000?
2 Answer(s) Available
Answer # 1 #
select first_name from emp where last_name IN ('A', 'B', . . . . . . '');
How many values we can pass with IN operator.
Answer: Oracle allows up to 1,000 IN list values in a SQL statement. However, using a long IN list may not be the most efficient way of processing such a query.
Instead, don't write your SQL with a long "IN" Lost. Instead, write the list to a global temporary table and join into the GTT:
You can also load the IN List" values into a PL/SQL collection and process this array as-if it was an Oracle table.
Answer # 2 #
- Use a (temporary) table. Load the values into another table first, then use the temp table in your IN condition: .
- Split the list into groups < 1,000.
- Use a multi-value IN list.
- Pass the values as one string and split it in SQL.
