Oracle APEX adding a conditional filter to a query

Question

How can I add a conditional filter to a query? I have a dropdown on the page and I want to filter the report query based on dropdown selected value. I am fine with that part (just add the item name to the query preceded by a column), but the things is that I want the filter not to be added if nothing is selected in the dropdown. how can that be accomplished?

Update:

Example of the WHERE clause:

WHERE ID=5 AND START_DATE=SYSDATE-:P1_NUM_OF_DAYS

if P1:NUM_OF_DAYS has been selected and if not, the data should be selected for all the dates

Answer 1

I believe what you are looking for is a Function based report. This would allow you to include a PL/SQL function body that would return the query from the table/view and also apply any filters (if any) based on page items values. This is a very common use for classic reports when you want the end users to have the flexibility of applying filters on the report on the fly.

Code goes something like this:

declare
q varchar2(4000);
begin
q:='select * from TABLE_NAME WHERE ID = 5
and ';
IF :P1:NUM_OF_DAYS IS NOT NULL THEN
q:=q||' NUM_OF_DAYS= :P1:NUM_OF_DAYS AND ';
END IF;
return q||'1=1';
END;

Just make sure you have the page items' value stored in the session. You can do it by submitting the page or by doing a 'redirect and set value' if you do want wish to have another button like 'Apply Filter'. Also, have the appropriate value for 'source used' depending on how you want to have this implemented.

Hope this helps!

Answer 2

If that dropdown (Select List) item's name is P1_DEPTNO, then

where (d.deptno = :P1_DEPTNO or :P1_DEPTNO is null)
  and ...