Oracle Apex - Select List - one option with multiple values-CodePudding

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.

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
     )

CodePudding user response：

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>