Oracle Apex - Select List - one option with multiple values

Question

I'm stuck at a trivial scenario in Oracle Apex(22.1.0-17).

I have a classic report with some "notifications", (as below) and with a flag - if some specific record in the table was read or not (theoretically) - Y/N values in the IS_READ column.

Notification report

I would like to archive full filtering of the records based on their flag, and that is why I used Select List (Select2 - Plug-in, to be honest), and basically, I have obtained what I wanted but there is a one missing option. In the select list, I would like to have the fully selectable "All" option in the select list which will pass both "Y" and "No" values, and allows to display of all of the records in the classic report.

I have tried to use LOV with a simple SQL query as below but it doesn't work.

SELECT 
IS_READ,
(CASE   WHEN IS_READ = 'Y' THEN 'Yes'
        WHEN IS_READ = 'N' THEN 'No'
        WHEN IS_READ = NVL(:P2_IS_READ, IS_READ) THEN 'All'
        END
) as IS_READ2
FROM
NOTIFICATION
group by IS_READ;

Can someone direct me on how can I implement this "All" option in the select list (but not as a "Display Null Value")?

Thanks in advance!

Answer 1

Here's one option:

Select list LoV query:

SQL> select 'Yes' d, 'Y' r from dual union all
  2  select 'No'  d, 'N' r from dual union all
  3  select 'All' d, 'A' r from dual;

D   R
--- -
Yes Y
No  N
All A

SQL>

Classic report query:

select *
from notification
where is_read = case when :P1_IS_READ = 'Y' then 'Y'
                     when :P1_IS_READ = 'N' then 'N'
                     when :P1_IS_READ = 'A' then is_read
                end
order by id;

---

To illustrate it (using SQL*Plus; switching to substitution variable), with sample table:

SQL> select * from notification;

        ID NAME   IS_READ
---------- ------ -------
         1 Little Y
         2 Foot   N
         3 Crisp  N
    

Read notifications:

SQL> select *
  2  from notification
  3  where is_read = case when '&&P1_IS_READ' = 'Y' then 'Y'
  4                       when '&&P1_IS_READ' = 'N' then 'N'
  5                       when '&&P1_IS_READ' = 'A' then is_read
  6                  end
  7  order by id;
Enter value for p1_is_read: Y

        ID NAME   IS_READ
---------- ------ -------
         1 Little Y

Not-read notifications:

SQL> undefine p1_is_read
SQL> /
Enter value for p1_is_read: N

        ID NAME   IS_READ
---------- ------ -------
         2 Foot   N
         3 Crisp  N

All notifications:

SQL> undefine p1_is_read
SQL> /
Enter value for p1_is_read: A

        ID NAME   IS_READ
---------- ------ -------
         1 Little Y
         2 Foot   N
         3 Crisp  N

SQL>

Answer 2

Here's what I usually do. I will have a standard Select list item (it doesn't have to be a Select2) with Yes/No static values (which return Y/N). Then I'll the item to display a null value which I will label as "All".

Something like this:

Then in the corresponding query, I will have a where clause like this:

select ...
from ...
where ...
and (:P2_IS_READ is null       -- will show everything when you select the "All"
     or :P2_IS_READ = IS_READ  -- only show what matches
     )