Page Comparison

...

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

Solution

Unfortunately, the The IN clause is not fully treated addressed in DBCF. It would mean that we should parse This would require parsing the query and replace replacing the keywords with the real IN values, something we found to be difficult to implement. But there's an alternative to that, and this is easy to address.The idea is that multiselect CFs .

However, multiselect Custom Fields return the array of values , converted to string, as a comma delimited list. That means that Thus, the simpler solution is to parse that this string. So let's create a function to do so:

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 basic idea is very simple, we just split the string based on string has been split by the delimiter. Create

Use your preferred tool to create the function in the DB using your favorite tooldatabase.

You can now use this in your SQL, in the CF 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}, ', '))) ....

Of course, the customfield_12345 is a multiple values CF.