Weird ADF 11g requirement addressed with left outer join and modern SQL join syntax

The functional requirement was a little unusual. The page should either show all master-records or – depending on the value of a parameter – it should show exactly one master-record joined with exactly one detail-record. The use case was valid – that was exactly the functionality that was required.

In terms of EMP and DEPT -I like to always simplify things to express them in EMP and DEPT terms – , we should either see all Departments (and no Employee data) or we should see the data for a specific Employee joined with the data for its corresponding Department. And of course we just one to create a single page, and make it as simple as possible to create that page.

A simplistic page that supports this functionality could like this:

when a specific Employee is requested and

when all Departments should be shown, because no single Employee is asked for.

The trick to easily deal with this challenge is not in the View but in the Model – and more specifically in the SQL that powers the underlying ViewObject. By making clever use of the ‘new’ (introduced in Oracle 9i in 2001 (!)) join syntax in SQL that gives us new functionality as well as the new syntax, it turns out to be quite simple to create a single ViewObject that queries either one joined record or multiple unjoined-records.

The steps to reproduce:

1. Create a JDeveloper Fusion Web Application. Create a Database Connection to SCOTT schema.

2. Create (default) Entity Objects on EMP and DEPT. Create an updateable ViewObject DepartmentsView on DEPT

Edit the ViewObject DepartmentsView

3. Add a Bind Variable with name bind_empno and of type Integer:

4. Edit the Query for the View Object

Set the Query Mode to Expert:

Edit the FROM and WHERE section of the query:

What the query now means is:

• get all Departments
• outer join these Departments – meaning that all Departments will live through this join step, even those that cannot be joined – to Employee whenever possible, based on two conditions:
  • 1. the value of deptno in Department should match the value of deptno in Employee
  • 2. the value of empno in Employee should match the value of the :bind_empno bind variable
• from the result – which always includes at least four Department records and possibly fifteen records (one for Department 40 and 14 for the Employees and their join with a Department) – filter all records that do not have the proper empno value (that is, when the bind variable has a value; when it does not, all records are allowed)

The join condition will not allow through any Employee records when the bind variable is NULL. In that case, all four Department records are returned because of the left outer join. When the bind variable is not null, only the Employee with that value in its empno attribute will survive the join, together with its associated Department – and along with the other three departments because of the outer join. Those three departments should be filtered out in the case of a value in bind_empno and that is what the WHERE clause takes care of.

5. Run the Application Module to see the ViewObject in action:

The tester appears. Double click the ViewObject.

Enter no value for bind_empno, just click on OK.

The departments are all shown – without associated Employee details.

Press the refresh icon. Re-click the ViewObject and this time type a value for the bind_empno variable, the EMPNO value for an existing Employee:

Press OK.

Now only a single record is returned. It consists of the selected Employee joined with the associated Department.

6. Create and run the Web Page

Creating the page is trivial now – the smartness is all in the ViewObject and really in the SQL.

After dragging the ViewObject based collection as a Form, also drag the ExecuteWithParams method as a parameter form:

Now run the page.

Without a value for the bind variable, all Departments are shown -without any employee details.

Enter a value for the bind_empno variable and press the ExecuteWithParams button. The details for Employee 7934 appear along with the associated Department.

Resources

Download the JDeveloper application (11.1.1.4): AllDeptsOrOneEmpPlusDept_OuterJoinedVO.