Querying by Nonbase Table Items

As a continuation to the sorting capability by foreign key look-up items discussed in the preceding section, I explore here the functionality of querying by nonbase items. The DEPT_NAME item in the EMPLOYEE block ( illustrated in the preceding section) is a nonbase table, and Forms default query processing does not apply to it. A typical requirement would be to query on all employees belonging to a particular department, based on DEPT_NAME. The DEPT_ID is not as suggestive as the DEPT_NAME in identifying a department.

With the introduction of the capability to change the DEFAULT_WHERE of a base table block dynamically, querying by nonbase table items can be accomplished with a PRE-QUERY trigger containing an explicit cursor for determining the foreign key column values corresponding to the nonbase table item value.

Tip

An alternative way to order by foreign key items or query by nonbase table items involves specifying an inline SELECT in place of the base table for the block, based on a stored procedure returning a result set. See Chapter 2, "Advanced GUI Development: Developing Beyond GUI," for more details regarding this technique.

 

Consider the standard DEPT and EMP application. You will outline the technique of querying by DEPT_NAME that is a nonbase table item in the EMPLOYEE block. Follow these steps:

1. Set the Query Allowed property to YES / TRUE for the item DNAME .
2. Derive the foreign key item DEPT_ID based on the ad hoc query input of :EMPLOYEE.DEPT_NAME. Use the POPULATE_DEPT_NAME procedure to populate the DEPT_NAME item for every DEPT_ID retrieved. Use a POST-QUERY trigger on the EMPLOYEE block to do this:

   POST-QUERY
   
   
   
   :employee.dept_name := populate_dept_name(:employee.dept_id);
   

3. In the PRE-QUERY trigger, set the DEFAULT_WHERE of the EMP block dynamically based on the resulting DEPTNO :

   PRE-QUERY
   
   
   
   DECLARE
   
    v_dyn_where VARCHAR2(100) := NULL;
   
   BEGIN
   
    v_dyn_where :=
   
    'WHERE deptno IN
   
    (SELECT deptno FROM dept WHERE dname = NVL(:emp.dname, dname) ');
   
    SET_BLOCK_PROPERTY('EMP', DEFAULT_WHERE, v_dyn_where);
   
   END;
   

4. Performing an Enter Query in the EMPLOYEE block and specifying a criteria like S% in the DEPT_NAME item followed by Execute Query would retrieve only those employees belonging to departments whose names start with uppercase S.

Tip

PRE-QUERY is fired even before the SELECT is constructed and is the best place to dynamically change the DEFAULT_WHERE and ORDER BY clauses. PRE-SELECT is fired after the SELECT is constructed and before it is issued for execution. The system variable :SYSTEM.LAST_QUERY holds this SELECT at this point in time. POST-SELECT fires after the SELECT has been executed but before records have been FETCHED . POST-FETCH fires repeatedly for each set of records fetched.