Using DBCF queries: IN clauses
Table of Contents |
---|
Info | ||
---|---|---|
| ||
DBCFPower Database Fields PRO™ 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. |
...
Unfortunately, the IN clause is not fully treated in DBCF. It would mean that we should parse the query and replace 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 return the array of values, converted to string, as a comma delimited list. That means that the solution is to parse that string. So let's create a function to do so:
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; |
...