JDeveloper: Creating a FULL OUTER JOIN View Object

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:

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
Image to visualize a full outer join

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.

Solution

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 solution

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 12.2.1.3 and uses the HR DB. The same technique can be used with other JDeveloper versions too.

Advertisements

1 thought on “JDeveloper: Creating a FULL OUTER JOIN View Object

  1. Pingback: Developer Partner Community Newsletter January 2019 – Developer Community

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.