I am trying to create a parameterized report, where I have a multi-select filter (:P1_FILT1). The values in the database are semi-colon(;) separated. and in APEX Item value separator is also semi-colon.
I want something like if someone searches for HLH department then it should return all the employees who are in HLH dept. but also returns those employees who are working in multiple dept. like 'HLH; HPU'.
I have used this query to filter in report:
select col1
, col2
, col3 (should match filter1 value)
from tab1
join tab2 on tab1.col1=tab2.col1
where (:P1_FILT1 is null or tab2.col3 in (select column_value from table(apex_string.split(:P1_FILT1 ,'; ')) where column_value is not null))
The output of the report after filtered value only shows the rows where it matches the string exactly and misses the rows where it is combined with other departments.
Filter: HLH
Desired Output: row 1: col1, col2, HLH; HPU
row 2: col1, col2, HLH
Actual Output: row 2: col1, col2, HLH