CODEWITHSUNDEEP
sqloracle-apex
Abha
Abha

Reputation: 347

Conditional where clause in Oracle SQL query

I have a query in APEX, which uses a where clause condition based on a item's value in apex. If the date is null then I want to show all the records from the table, and if the date is not null i want to add where conditions based on that value. How can I do that ?

I want to add below 2 conditions if the P2_SELECT_DATE is not null : 

select * from HR_DATA
where 
     :P2_SELECT_DATE between  person_eff_start_date and person_eff_end_date
 and :P2_SELECT_DATE between nvl(assign_eff_start_date,sysdate-1) and nvl(assign_eff_end_date, sysdate+1)

And if the P2_SELECT_DATE is null then I don't want to have these conditions in the where clause at all.

Upvotes: 1

Views: 2144

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132750

You can do this:

select * from HR_DATA
where (:P2_SELECT_DATE is null
      or (:P2_SELECT_DATE between person_eff_start_date and person_eff_end_date
         and :P2_SELECT_DATE between nvl(assign_eff_start_date,sysdate-1) 
                                 and nvl(assign_eff_end_date, sysdate+1)
      )

Sometimes if there are a lot of optional filters it can be more efficient to build the query dynamically:

declare
    q long;
begin    
    q := 'select * from HR_DATA where 1=1';

    if :P2_SELECT_DATE is null then
       q := q || ' and :P2_SELECT_DATE between person_eff_start_date and person_eff_end_date'
              || ' and :P2_SELECT_DATE between nvl(assign_eff_start_date,sysdate-1)'
              || ' and nvl(assign_eff_end_date, sysdate+1);
    end if;
    return q;
end;

The "where 1=1" is just a trick so that any conditionally appended filters alsways start with " and".

Upvotes: 2

Related Questions