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
...
| Note |
|---|
...
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.
| Code Block | ||
|---|---|---|
| ||
//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.
| Code Block | ||
|---|---|---|
| ||
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. |
...
| Tip |
|---|
For further reading, go to Using comma separated value parameter strings in SQL IN clauses. The example above relies on the same solution, but oriented to SQL Server. |