On my todo list, I found a topic which I wanted to blog about for a long time. The problem is how to create a ViewObject, based on EntityObjects, which builds a full outer join between two tables.
For those of you who don’t know about full outer joins in SQL here is a short description from https://www.w3schools.com/sql/sql_join_full.asp:
The FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or right (table2) table records.
Note: FULL OUTER JOIN can potentially return very large result-sets!
FULL OUTER JOIN Syntax:
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
There are not too many use cases where you need to use a full outer join, but they exist (e.g. https://searchoracle.techtarget.com/answer/Another-good-FULL-OUTER-JOIN-example or to compare two or more tables).
Problem: How can a full outer join be created in ADFbc?
I show how to create a VO based on Employees and Department EO using a full outer join on the department_id. This VO will return all departments with all their employees, departments which don’t have any employee and all employees who don’t have a department.
Following the syntax from above, we use an SQL statement like
SELECT Departments.DEPARTMENT_ID, Departments.DEPARTMENT_NAME, Employees.DEPARTMENT_ID AS DEPARTMENT_ID1, Employees.LAST_NAME, Employees.FIRST_NAME, Employees.EMPLOYEE_ID FROM DEPARTMENTS Departments FULL OUTER JOIN EMPLOYEES Employees ON Departments.department_id = Employees.department_id ORDER BY Departments.department_id, Employees.last_name;
There are other SQL statements which produce the same result like
SELECT DISTINCT * FROM (SELECT d.department_id AS d_dept_id, d.DEPARTMENT_NAME, e.department_id AS e_dept_id, e.last_name last_name, e.FIRST_NAME FROM departments d LEFT OUTER JOIN employees e ON d.department_id = e.department_id UNION ALL SELECT d.department_id AS d_dept_id, d.DEPARTMENT_NAME, e.department_id AS e_dept_id, e.last_name, e.FIRST_NAME FROM departments d RIGHT OUTER JOIN employees e ON d.department_id = e.department_id ) ORDER BY d_dept_id, last_name;
The statement combines a left outer join with a right outer join. The ‘Select distinct….’ is used to eliminate duplicate rows which are returned for both joins. Anyway, the results are equal.
Now we can build the view object based on the two entity objects (Departments and Employees). We start by creating a new view object
and fill in the name as ‘DepEmpViewObj’. Make sure you select ‘Entity’ as ‘Data Source’
On the next wizard page shuttle the Departments and the Employees entities to the right
Now select the Departments entity and you get
Selecting the Employees entity you get
This we have to change as the join type is ‘inner join’ and not what we like to do. If you select the drop down menu you see
Hm, there is no ‘full outer join’ as joint type. We can’t create this type of join declaratively, we have to do this directly with a SQL statement. So, drop down the ‘Association’ field and select ‘none’
The final definition is
On the next page select the attributes
We don’t change anything in step 4 so we go to step 5. Here uncheck the ‘Calculate…’ checkbox and select the ‘Write Custom SQL’
Now we copy the SQL statement from above and copy it into the text area after deleting the current statement. Don’t forget to delete the ‘order by…’ part from the ‘Select:’ text area and add them into the ‘Order By:’ text field
We skip the steps 6,7 and 8 and add the view object to the application module in step 9
Finally, we finish the wizard and are ready to test the view object.
Running the application module in the tester show the resulting table (only the last ~40 rows are shown)
We see departments without employees and we have one employee (see the last row) without an assigned department. All as expected.
To complete the application we add the new DepEmpViewObj onto a page as a table. Running it we get the same result as in the tester.
You can download the sample from GitHub BlogFullOuterJoin. The sample was built using JDeveloper 220.127.116.11 and uses the HR DB. The same technique can be used with other JDeveloper versions too.