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.

pic5.1

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.

pic5.2

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.

 

pic5.3

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

pic5.4

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.

 

pic5.5

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.

pic5.6

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

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s