Using Database custom fields queries: IN clauses
Using DBCF queries: IN clauses
Â
Required apps
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
Â
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:
Â
//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:
SELECT .... FROM ..... WHERE columnName IN (select column_value from table(splitString({customfield_12345}, ', '))) ....
Of course, the customfield_12345 is a multiple values CF.Â
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
- Copy field value from parent
- Default values of (system) fields on create screen
- Filter the users from a database
- Make a custom field required based on another custom field
- Reset a field
- Restrict a select list to not show certain values
- Rolling summation or subtraction between parent and child fields
- Selecting users member of the reunion of 2 sets intersected with a third one
- Use Live Fields to initialise field values for a transition screen