Oracle Apex 5.0 Custom Search in Classic Report

Question

I'm using Oracle APEX 5.0 and I'm trying to built a classic report with custom search based on more than one criteria like (From Date, To Date, Serial And Emp Name(Selected from List and returns ID)), I wrote the Conditions Like :

where   emp_id = nvl(:P73_EMP_ID,emp_id)

or      ror_serial like nvl('%'||:P73_SERIAL||'%',ror_serial)`

or      ror_effective_date between to_date(:P73_FROM_DATE) and to_date(:P73_TO_DATE)

and     ror_approved_flag = 'N'

The problem is that I need The search based on one or more Criteria, That means I may left some items empty, and if all the items are empty, I need the whole records to be shown, Thanks For help.

Tony Andrews Tony Andrews · Accepted Answer · 2015-09-17T12:54:50

There are two ways to do this:

1. "Quick and Dirty" way

This is fine as long as you are dealing with small data sets where performance will not be an issue. Simply allow for nulls in your conditions e.g.

 and (:P73_FROM_DATE is null or ror_effective_date >= to_date(:P73_FROM_DATE))
 and (:P73_FROM_DATE is null or ror_effective_date <= to_date(:P73_TO_DATE))

and so on.

2. Dynamic SQL

This is better for larger data sets where performance could be an issue. Change the report source type to "PL/SQL function returning SQL query". Then change the source to look like this

declare
   q long;
begin
   -- The query with any conditions always applied
   q := 'select a, b, c from mytable where ror_approved_flag = ''N''';

   -- Append any optional conditions
   if :P73_FROM_DATE is not null then
      q := q || ' and ror_effective_date >= to_date(:P73_FROM_DATE)';
   end if;
   -- etc.

   return q;
end;

This will result in an appropriate query being generated based on the criteria you have. This means that Oracle can choose the most appropriate query plan for the criteria.

Oracle Apex 5.0 Custom Search in Classic Report

2 Answers

1. "Quick and Dirty" way

2. Dynamic SQL