Overcome Oracle IN clause limit

If you’ve ever worked with Oracle database probably you’ve encountered IN clause limitation set to 1000 values. You can resolve this issue with a subquery but what if you want to feed results from one query into another query’s IN clause and you hit the limit of 1000?

I had a requirement where I had to filter one database query based on values from an excelsheet which number of records exceeded the limit. I decided to use a Webi solution. In my example I will use a short list of values held in excel. The main query which is going to have IN clause has one column with City names. I will aim to filter out the City values which don’t exist in my second worksheet.


My second worksheet has a limited list of cities and one additional column. In my case I created a dummy flag with all values set to 1.


Now we need to build two queries based off these two spreadsheets and merge a common dimension. Also we want to convert a flag object into a detail object and set as an attribute of the merged City object.



Now if we drag a merged dimension along with a detail into a table we will come up with the following view:


City values which exist in a second worksheet have a flag set to 1. Now depending if we want to show or hide values from the second worksheet we can create a filter on a table where a detail object is null or not null.



Let’s say we want to show only values from the second worksheet. In this case we define a filter on a detail where “v_filter Is not null”. We don’t need to keep the flag column in the table so it can be removed.


This way without any SQL modifications we can easily create a filter on a column with more than 1000 values.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s