Oracle SQL: How to use more than 1000 items inside an IN clause
oracle, sql
Solution
Not sure that using so many values in a `IN()` is that good, actually -- especially for performances.
When you say "the results are strange", maybe this is because a problem with parenthesis ? What if you try this, instead of what you proposed :
SELECT ...
FROM ...
WHERE ...
AND (
ep_codes IN (...1000 ep_codes...)
OR ep_codes IN (...200 ep_codes...)
)
Does it make the results less strange ?
Problem
I have an SQL statement where I would like to get data of 1200 `ep_codes` by making use of `IN` clause. When I include more than 1000 `ep_codes` inside IN clause, Oracle says I'm not allowed to do that. To overcome this, I tried to change the SQL code as follows: ``` SELECT period, ... FROM my_view WHERE period = '200912' ... AND ep_codes IN (...1000 ep_codes...) OR ep_codes IN (...200 ep_codes...) ``` The code was executed succesfully but the results are strange (calculation results are fetched for all periods, not just for 200912, which is not what I want). Is it appropriate to do that using `OR` between `IN` clauses or should I execute two separate codes as one with 1000 and the other with 200 ep_codes? Pascal Martin's solution worked perfectly. Thanks all who contributed with valuable suggestions.