There have been numerous questions about how to implement a SQL IN clause in ADF using a viewCriteria since the begin of life of ADF. There are a couple of solutions e.g. using an SQL array type or a DB table to store the values of the IN clause.
I came up with another solution which was using Oracle DBs CATSEARCH function or even CONTAINS search index.
All those solutions are more or less complex and need some programming to implement.
The solution I present in this blog is easy and elegant. However, it has its limitations still. Anyway, for about 90% of the use cases, I know where you want to add an SQL IN clause it works perfectly.
The problem is that you can’t simply define an IN clause in a query or ViewCriteria like
Select * from Employees where employee_id in (:pListOfValues)
Using this select statement as a query for a view object will not throw an error, but it won’t get you the desired result.
If you use a String type parameter for ‘pListOfValues’, e.g. “100, 110, 200” the query in the VO would look like
Select * from Employees where employee_id in (“100,110,200”)
And return nothing as a result. It is easy to see why: the parameter is expanded as a string, not a list of numbers. If you think you could overcome this by converting the employee_id to a string it will still not work as “100” IN “100, 110, 200” still won’t work.
A very elegant way to solve this problem is to change the query or where clause to
SELECT * FROM TABLE WHERE COLUMN IN ( SELECT regexp_substr(:pListOfValues,'[^,]+',1,level) FROM dual CONNECT BY regexp_substr(:pListOfValues,'[^,]+',1,level) IS NOT NULL)
The work is done by the select statement in the IN clause, This statement will split a comma-separated string in a series of values like a sub-select. If you run
SELECT regexp_substr(:pListOfvalues, '[^,]+', 1, level) FROM dual CONNECT BY regexp_substr(:pListOfvalues, '[^,]+', 1, level) IS NOT NULL
In a SQL worksheet and pass ‘11,12,15,17’ as ‘pListOfValues’ you get
You can pass any comma-separated string e.g. “1, hello, 444, world” and get
The SQL builds an internal table and add the values delimited by a comma to it. This internal table can then be used in the IN clause of another SQL statement.
Finally, running a complete query we can search e.g. for the employees which have the ID 100 or 110, or 180 or 176
In part 2, I’ll show how to implement this kind of query in a ViewCriteria of a ViewObject by adding a custom operator.