Reputation: 347
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
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