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.
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.
Public Function Group1(ByVal parameter as Parameter) as String Dim s as String s=String.Empty If parameter.IsMultiValue then For i as integer = 0 to parameter.Count/2 s = s "" CStr(parameter.Value(i)) + "," Next Else s = CStr(parameter.Value(0)) End If Return s End Function Public Function Group2(ByVal parameter as Parameter) as String Dim s as String s=String.Empty If parameter.IsMultiValue then For i as integer = (parameter.Count/2) to parameter.Count-1 s = s "" CStr(parameter.Value(i)) + "," Next Else s = "" End If Return s End Function
New we use two SQL queries including the two hidden parameters joined by the UNION operator
SELECT * FROM TABLE_1 WHERE VALUE_1 IN (:prmHiddenInspectorFirstGroup1) UNION SELECT * FROM TABLE_1 WHERE VALUE_1 IN (:prmHiddenInspectorFirstGroup2)
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.