Show all rows as default in calendar in Oracle Apex

Question

I'm creating a report table type calendar where users can create back up by date select a filter that would filter out the table values depending on the user selected. (i.e. if they choose user1, then only back ups with user1 will show up)

I would like it to be when P106_BACK_UP_BY_USER = 0, the table shows all the values (aka getting rid of the "where" portion of the query. Thank you for your help!

I'm having issues with trying to allow the user to see all the back ups of the table again (getting rid of the filtered value). My current query is this:

I would like it to be when P106_BACK_UP_BY_USER = 0, the table shows all the values (aka getting rid of the "where" portion of the query. Thank you for your help!

Answer 1

You can use case when statements in your query's where condition as follows:

select *
from my_table
where my_table.created_by = 
        (select user_name from my_table2 where app_users_id =  
         case :P106_BACKUP_BY_USER when 0 then app_users_id 
         else :P106_BACKUP_BY_USER
         end)

And for getting better help, please paste your code as text not as an image next time.

Answer 2

This should work too:

...
WHERE b.active_server = s.server_id
  AND (:P106_BACK_UP_BY_USER = 0 OR
       UPPER(b.created_by) = 
         (SELECT UPPER(user_name) 
            FROM eba_bt_app_users
           WHERE app_users_id = :P106_BACK_UP_BY_USER
         )
      );