Filter by Multiple Values

Filter by Multiple Values Using Split Function

Topic Introduction: "Filter by Multiple Values using Split Function", In oracle SQL there is a term Selection which is a condition to find out specific data that users want. on the report we give parameters to the user to filter data. as usual we use single-value parameters. but sometimes user wants to filter by multiple values in one parameter, such as the user wants to see Employee of IT and ADMINISTRATION departments. in this case for multiple value parameters in the Oracle apex field, we can use this tutorial instruction.  

Split Function Creation: 

create or replace function split (i_str in varchar2, i_delimiter in varchar2 default ',') 

  return sys.odcivarchar2list

  pipelined

  as

    l_current_string varchar2(4000) := i_str;

    l_pos            binary_integer;

  begin

    if i_str is null then

        return;

    end if;

    loop

      l_pos := nullif(instr(l_current_string, i_delimiter), 0);

      pipe row(substr(l_current_string, 1, nvl(l_pos - 1, length(l_current_string))));

      exit when l_pos is null;

      l_current_string := substr(l_current_string, l_pos + length(i_delimiter));

  end loop;

end split;

Example of the filter by multiple values using on Where Condition:

where A.LOCATION_CD in (select column_value from table(split(nvl(:P_LOC,A.LOCATION_CD),':')))