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.