ORACLE APEX SQL QUERY

Question

I have a question regarding an sql statement that will be executed in Oracle Application Express. I have this query that lists test runs based on JIRA Key and excluding some that I specified:

 select name as d, test_run_id as r From test_run 
Where project_jira_key=:TR_BASED_ON_PROJECT and folder_id NOT IN(select folder_id from folder where name in (select column_value from table (apex_string.split(config.get('ATM_DASHBOARD_CONFIGURATION_FOLDER_EXCLUDE'), ','))));

I would like to add a CASE or IF expression that would execute if project_jira_key=null (not given by user)

 select name as d, test_run_id as r From test_run 
Where folder_id NOT IN(select folder_id from folder where name in (select column_value from table (apex_string.split(config.get('ATM_DASHBOARD_CONFIGURATION_FOLDER_EXCLUDE'), ','))));

Basically the second part of the code is the same, it just does not select a specific 'project_jira_key' OR selects all project_jira_keys. In other words: Do the first query, but if project_jira_key is not given, execute the second query. Thanks in advance

Answer 1

You can do this by combining the where predicates:

select name as d, test_run_id as r From test_run 
 where ( project_jira_key = :TR_BASED_ON_PROJECT and folder_id not in ( ... ))
    or ( project_jira_key is null and folder_id NOT IN ( ... ))