Use Database Custom Fields queries with IN clauses

Problem

You want to write an IN clause into the SQL statement configured on a Database Custom Fields (DBCF) field, using a specific multiselect field.

Solution

This solution applies to Oracle only, but equivalents can be used on any database.

The IN clause is not fully addressed in DBCF. This would require parsing the query and replacing the keywords with the real IN values.

However, multiselect Custom Fields return the array of values converted to string, as a comma delimited list. Thus, the simpler solution is to parse this string.

To do this, create the following function.

//Oracle type
CREATE OR REPLACE TYPE type_string_array AS TABLE OF VARCHAR2(2000);

//Our split function
CREATE OR REPLACE FUNCTION splitString (s_delimited_list_in VARCHAR2, s_delimiter_in VARCHAR2)
  RETURN type_string_array PIPELINED
IS
 l_idx               PLS_INTEGER;
 l_list              VARCHAR2(32767) := s_delimited_list_in;
 l_value             VARCHAR2(32767);
 ls_delimiter        VARCHAR2(100) := NVL(s_delimiter_in,',');
BEGIN
  LOOP
    l_idx := INSTR(l_list,ls_delimiter);
    IF (l_idx > 0) THEN
      PIPE ROW(SUBSTR(l_list,1,l_idx-1));
      l_list := SUBSTR(l_list,l_idx+LENGTH(ls_delimiter));
    ELSE
      PIPE ROW(l_list);
      EXIT;
    END IF;
  END LOOP;
RETURN;
END splitString;

As you can see in this example, the string has been split by the delimiter.

Use your preferred tool to create the function in the database.

You can now use this in your SQL, in the Custom Field SQL configuration. Note that the customfield_12345 is a Custom Field with multiple values.

SELECT ....
FROM .....
WHERE columnName IN (select column_value from table(splitString({customfield_12345}, ', '))) ....

For further reading, go to http://www.codeproject.com/Tips/584680/Using-comma-separated-value-parameter-strings-in-S. The example above relies on the same solution, but oriented to SQL Server.