6.5.4 Optimal Approach to Query by Example

Two approaches exist for query-by-example WHERE clause filters. One requires less typing; the other performs better.

The simpler-to-understand technique shown below uses an OR clause that first checks if the page item value IS NULL and then provides a filter on the desired column involving the page item bind variable.

/* Either no department is selected,
or match the selected department */
(   :P5_SELECTED_DEPARTMENT IS NULL 
 OR DEPTNO = :P5_SELECTED_DEPARTMENT)

When processing a query for the first time, the database considers bind variable values when deciding which indexes to use for fast data retrieval. Then, it reuses this "query plan" on subsequent executions of the same SQL statement, even if the bind variable values have changed. This means that using the simpler approach above can result in not always using the best index for the filtering job at hand, especially when your query might contain many such clauses.

For the best performance, include a query by example filter clause only when it is relevant. In other words, if the corresponding page item value is not null, then include the clause that filters a column based on its value. Otherwise, if the page item value is null, leave that filtering clause out of the query.

Employing this strategy requires a dynamic approach to constructing the SQL query. APEX lets you write a Function Body Returning SQL to easily implement this technique. Instead of specifying the SQL statement verbatim, you use PL/SQL to build it up as needed and return the resulting SQL statement. For example, the code below returns a SQL statement that includes the filter predicate if the P5_SELECTED_DEPARTMENT page item value is not null, or leaves it out otherwise.

return q'~
SELECT EMPNO, ENAME, SAL, DEPTNO, HIREDATE
  FROM EMP
 WHERE 1=1
~'
|| CASE WHEN :P5_SELECTED_DEPARTMENT IS NOT NULL THEN
q'~
AND DEPTNO = :P5_SELECTED_DEPARTMENT
~' END;   

The code above is what you would type into the Property Editor for a region's data source after selecting the Function Body Returning SQL option. Notice it's using the PL/SQL syntax for a multi-line string literal using q'~ to begin the string and ~' to end it. This makes the parts of your SQL statement being concatenated more readable and avoids the need to handle single quotes in a special way. The double vertical bar symbol is the Oracle SQL string concatenation operator. Finally, recall that the SQL CASE expression returns null if none of its WHEN or optional ELSE conditions evaluates to true, and that concatenating a null value to a string leaves the original string unchanged.

At runtime, whenever your region retrieves its data the APEX engine "wraps" your code with an enclosing "envelope" shown below. Then it evaluates the function to compute the effective text of the SQL statement it asks the database to execute.

function x
return varchar2
is
begin
   /* Your Function Body Here */
end x;

Using this technique, when P5_SELECTED_DEPARTMENT is null the database sees the query:

SELECT EMPNO, ENAME, SAL, DEPTNO, HIREDATE
  FROM EMP
 WHERE 1=1

In contrast, when P5_SELECTED_DEPARTMENT has a non-null value the query executed is:

SELECT EMPNO, ENAME, SAL, DEPTNO, HIREDATE
  FROM EMP
 WHERE 1=1
AND DEPTNO = :P5_SELECTED_DEPARTMENT

You can adopt the same technique to conditionally include multiple filter clauses. For example, the following function body evaluates three different query by example page items to include the relevant filter clauses. Using the always true predicate WHERE 1=1 ensures a syntactically correct query for any number of added filters. It also lets each conditionally-included predicate uniformly include the leading AND conjunction.

return q'~
SELECT EMPNO, ENAME, SAL, DEPTNO, HIREDATE
  FROM EMP
 WHERE 1=1
~'
|| CASE WHEN :P5_SELECTED_DEPARTMENT IS NOT NULL THEN
q'~
AND DEPTNO = :P5_SELECTED_DEPARTMENT
~' END
|| CASE WHEN :P5_ENAME_CONTAINS IS NOT NULL THEN
q'~
AND INSTR(UPPER(ENAME), UPPER(:P5_ENAME_CONTAINS)) > 0
~' END
|| CASE WHEN :P5_MAX_SALARY IS NOT NULL THEN
q'~
AND SAL <= :P5_MAX_SALARY
~' END; 

To maximize maintainability, you can put your query-building code into a PL/SQL package function. Imagine you have a UTILS package, and added to its specification and body the p5_employees_query function shown below. In that case, your region's function body returning SQL becomes:

return utils.p5_employees_query(:P5_SELECTED_DEPARTMENT,
                                :P5_ENAME_CONTAINS,
                                :P5_MAX_SALARY);

The p5_employees_query function would look like this:

function p5_employees_query(
   p_selected_deptno in number,
   p_ename_contains  in varchar2,
   p_max_salary      in number)
   return               varchar2
is
begin
   return q'~
   SELECT EMPNO, ENAME, SAL, DEPTNO, HIREDATE
     FROM EMP
    WHERE 1=1
   ~'
   || CASE WHEN p_selected_deptno IS NOT NULL THEN
   q'~
   AND DEPTNO = :P5_SELECTED_DEPARTMENT
   ~' END
   || CASE WHEN p_ename_contains IS NOT NULL THEN
   q'~
   AND INSTR(UPPER(ENAME), UPPER(:P5_ENAME_CONTAINS)) > 0
   ~' END
   || CASE WHEN p_max_salary IS NOT NULL THEN
   q'~
   AND SAL <= :P5_MAX_SALARY
   ~' END; 
end p5_employees_query;

Whenever your function body might include a reference to a page item value as a bind variable in a region data source query or WHERE clause, list that item in the region's Page Items to Submit property. Omit the colon and comma-separate multiple item names. If you forget this step, the bind variable evaluates to null on refresh and the query may return no rows.

Caution:

When App Builder checks the syntax of a function body returning SQL in Page Designer, all of the referenced bind variables will evaluate to null. Therefore, it's important that you always return a syntactically-valid SELECT statement even in this situation.