Joining Oracle E-Business Suite HRMS Tables in Oracle SQL Developer

Shannon MiskevicsOfferings, Oracle, Technical TipsLeave a Comment

Joining Oracle E-Business Suite HRMS Tables in Oracle SQL Developer

JOINS

In SQL Developer, JOINS are used to relate information in different tables. A JOIN condition retrieves data from two or more tables. It is performed in the WHERE clause of the select statement. There are multiple types of SQL joins, but this post is going to be focusing on the OUTER JOIN.

SYNTAX

syntax
The (+) operator can be on either the right or left side of the join condition. The side the operator needs to be on depends on whether “Nulls” need to be included from that table.
table circles
Using the first SQL JOIN, the results would return all the records from table 2 and only those records in table 1 that intersect with table 2.

In the second illustration, it would display opposite results. All the records in table 1 would appear and only those records in table 2 that intersect with table 1.

USING JOIN QUERIES: EXAMPLE

When displaying all of the employees whose last name is “Smith” along with their assignment number and position, the SQL query would be as shown below:
sql q
The PERSON_ID in the PER_ALL_PEOPLE_F table joins with the PERSON_ID in the PER_ALL_ASSIGNMENTS_F table. Also, the POSITION_ID in the PER_ALL_ASSIGNMENTS_F table joins with the POSITION_ID in the HR_ALL_POSITIONS_F table.

The SQL query will return all of the people from the specified tables along with their positions and assignments, even if the person has not been given an assignment. Using the (+) operator with the hapf.POSITION_ID will display NULL values even when there is no matching condition. The (+) must be included with the hapf.EFFECTIVE_START_DATE & END_DATE because it allows NULL to be shown for the positions if the people are applicants.

When joining tables that are date-tracked (shown with _F), the effective dates must be specified for the query. In this case, it is using today’s date (sysdate).

The output of the query would look like this:
sql

Leave a Reply

Your email address will not be published. Required fields are marked *