Using DBCF queries: IN clauses

Problem

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

Solution

...

Note
The following This solution applies to Oracle only, but equivalents can be found in used on any decent database.

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.

Code Block
language sql
//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. 

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

Of course, the customfield_12345 is a multiple values CF. 

Note
Note: a reader pointed to the following article: http://www.codeproject.com/Tips/584680/Using-comma-separated-value-parameter-strings-in-S. This is pretty much the same idea, but for the SQL server.

See also

...