Entries by OptimusBI

Include more than 1000 Values in Oracle IN Clause

In Oracle we can’t include more than 1000 values in the “IN” clause. To get around this limitation, you divide the string of comma delimited values to be included into two or more parts using SUBSTR or through any similar other function and then these new strings should be placed in the IN clause of different SQL statements that are later combined using the UNION operator. Sample Code First create two hidden parameter that include the initial first and second half of the selected values of the parameter. For this write a custom code can be used. Sample code to break the parameter into two hidden parameter of half the original length. Sample Query New we use two SQL queries including the two hidden parameters joined by the UNION operator Obviously, this example breaks when you get to 2000 values, but it is presented here as a sample approach that can be easily modified to suit more advanced situations.

The post Include more than 1000 Values in Oracle IN Clause appeared first on OptimusBI.