Versions Compared

Old Version 1

changes.mady.by.user Service Account

Saved on

compared with

New Version Current

changes.mady.by.user Elena Kolesnik

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Using DBCF queries: IN clauses

Table of Contents

 

You will need the following JIRA plugins:
Info
titleRequired plugins
apps

DBCF

Level: BASIC

Problem

You have a multiselect field and you require to write an IN clause using that field into the SQL statement configured on the DBCF field.

Solution / Idea

 

Note

The following applies to Oracle only, but equivalents can be found in any decent database.

...

Code Block
languagesql
//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, the basic idea is very simple, we just split the string based on the delimiter. Create the function in the DB using your favorite tool.

 

You can now use this in your SQL in the CF SQL configuration:

...

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