Joining Records
Query Manager enables you to create queries that include multiple-table joins. Joins retrieve data from more than one table, presenting the data as if it came from one table. PeopleSoft Query links the tables, based on common columns, and links the rows on the two tables by common values in the shared columns.
Joins are what make relational databases relational. Using joins, you define relationships among fields when you query the records, not when you create the records. Because PeopleSoft records are highly normalized (they each describe one kind of entity), you can easily use Query Manager to create joins.
The procedure for joining tables differs depending on how the tables that are being joined are related to each other. Query Manager recognizes three types of joins: record hierarchy, related record, and any record.
A record hierarchy join joins a parent table to a child table. (A child table is a table that uses all the same key fields as its parent, plus one or more additional keys.)
Note: You can specify the parent/child relationship using the Parent Record Name option in PeopleSoft Application Designer.
Image: Selecting the second record
This example shows how to perform hierarchy joins with the ABSENCE_HIST record is selected.
To create a record hierarchy join:
In Query Manager select the base record for your query.
Select the appropriate fields and criteria.
From the Query page, click the Hierarchy Join link.
All of the records that have a parent/child relationship with your selected record appear.
Select the second record for the join.
The join is reflected on the Query page.
In a related record join, you can automatically join two records based on a relationship that has been predefined in the record designer. For example, if a field has a prompt table defined for it, PeopleSoft Query displays a join link to the right of the shared field.
Image: Query page showing related record join links
This example shows the Query page. Use the Join <record name> link to join record. The QEDMO Employee record can join to the JobCode record by using the by QE_JOBCODE field, or the QEDMO Employee record can join the Data Department record by using the DEPTID field.
To create a related record join:
In Query Manager select the base record for your query.
Select the appropriate fields and criteria.
From the Query page, click the Related Record Join link.
From the Select Join Type page, select the standard join option and click the OK button.
The join is reflected on the Query page.
Using Query Manager, you can create a join between two records (any record join) by selecting your initial base record, defining its output fields and associated criteria, and then returning to the Records page to select the second record. When you return to the Records page, you see the link Join Record rather than Add Record to the right of all listed record names.
If you have the Enable Auto Join preference selected in the Query Preferences page, PeopleSoft Query automatically attempts to join the new record to the existing record by looking for matching columns on the two records.
To access the Query Preferences page, click the Preferences link on any page of Query Manager.
An outer join forces a row from one of the participating tables to appear in the result if no matching row exists.
Note: You can create an outer join using this method only if you have an Oracle database. If you use a platform other than Oracle database, you can create an outer join equivalent or create a left outer join using PeopleSoft Query's left outer join feature.
The Outer Join functionality is available only in Query Manager. This functionary is not available in the 2–3 Tier Query Client.
See Creating Left Outer Joins.
To create an outer join, you must include a plus sign in parentheses (+) after the key fields of the subordinate record in the criteria that link the records. To produce this syntax, you cannot use the predefined joins. You must perform an any record join and code an expression that contains the (+) instead of a field.
You can join only one child record to the same parent query when the child record is added using the Records page. If you join more than one child record to the same parent query, an error message appears that says: "Left Outer joins must be joined to the last record in the query". On the contrary, you can have multiple outer joins to the same parent table if those joins are added using the Hierarchy joins link in the Query page.
Image: Define Expression section
This example illustrates the fields and controls on the Define Expression section.
Creating an Outer Join for Oracle Database
To create an outer join for Oracle database:
Access the Criteria page
From the Criteria page, click the Edit button to the right of the required field from the primary record.
In the Condition Type drop-down list, accept the default operator value of Equal To.
In the Expression 2 Type section, select the value of Expression.
The Define Expression text box appears.
Click the Add Field link.
Select the required field from the secondary record.
Manually enter (+) after the field name.
Click the OK button.
Creating an Outer Join Equivalent for non-Oracle Platforms
If you use a platform other than Oracle, use this method to achieve the same effect as an outer join.
Note: If you use a platform other than Oracle, you can also create a left outer join using PeopleSoft Query's left outer join feature.
For example, to retrieve a list of all students and any evaluation information on file, the first select must retrieve those students who have not completed an evaluation; the second select must retrieve those students who have completed an evaluation.
To perform an outer join equivalent for the previous example:
In Query Manager create a new query or updating an existing query using the Student_Data record.
Select the Customer_ID and Name fields.
Because you will select two different fields from the Student_Eval record and you want them to have their own columns, you must create two dummy fields for your first record.
Create two different expressions and select them for output.
Save the query.
Create your subquery to find those students who have not completed an evaluation.
This completes your first select. If you run the query at this point, you see only those students who did not complete an evaluation.
To join in the students who did complete an evaluation, create a union.
Using the Student_Data record, select Customer_ID and Name for output.
Join the Student_Eval record using a record hierarchy join.
Select Course and Course_Rt for output.
PeopleSoft Query enables you to easily create a left outer join. In a left outer join, all rows of the first (left) record are present in the result set, even if no matches are in the joining record.
Note: This feature is not available for the Oracle platform.
To add a left outer join to a record:
Access the Query Manager search page by selecting
Click the Create New Query link, add a record, and select fields.
Alternatively, open an existing query.
Access the Records page.
Find the joining record and then click the Join Record link on the same row as that record.
Select the Join to get additional fields only (Left outer join) option.
Note: You can add a left outer join to any record in a query.
Complete the join.
If the Enable Auto Join preference is selected on the Query Preference page, click the record name to join with.
If the Enable Auto Join preference is not selected on the Query Preference page, click the OK button.
Define the join criteria.
If the Enable Auto Join preference is selected, the Auto Join Criteria page appears.
You can clear the criteria from the query. You can also click the Add Criteria button to add or edit criteria or conditions. If no common keys are between the two join records, a message appears instead of the Add Criteria page.
If the Enable Auto Join preference is not selected, the Auto Join Criteria page does not appear.
If you want, navigate to the Criteria page to add criteria to the ON clause of the outer join. The This Criteria Belongs To drop-down list specifies where the criteria will appear. Select the alias that corresponds to this join record.
Define the effective date criteria.
For 2–3 tier client: If the joined record is an effective-dated record, the Effective Date Criteria page appears.
You can accept the defaults or change them as desired.
For 4–tier PeopleSoft Pure Internet Architecture: If the joined record is an effective dated record, a message appears stating that an effective date criteria has been automatically added.
Optionally, you can navigate to the Criteria page to change the defaults for this criteria.
To ensure that the left outer join finished successfully, navigate to the Query page.
Text for the joined records confirms a successful left outer join.
(Optional) If the query has multiple joins to the same security record, which can be resource intensive and time consuming when it runs, select the Security Join Optimizer option to improve the performance of this query.
If you use PeopleSoft Query with the DB2 UDB platform, the DB2 UDB platform does not allow you to have a subquery in the ON clause of a left outer join. For example:
SELECT A.ROLENAME, A.DESCR
FROM (PSROLEUSER B LEFT OUTER JOIN PSOPRDEFN C ON B.ROLEUSER
IN (SELECT F.OPRID FROM PSOPRDEFN F WHERE F.OPRID IN ('AMA1','AMA2','AMA3')))
This code yields the following error message: “SQL0338N An ON clause associated with a JOIN operator or in a MERGE statement is not valid.”
Note: Use the SELECT DISTINCT operation when you create a left outer join on two records with
two separate security records. Using the SELECT operation
in this case will cause the query to pull in too many records.
PeopleSoft Query automatically
includes an OR
IS NULL condition to make sure all relevant records are included.
Adding Left Outer Joins Criteria into the ON Clause
If you want to create criteria for the left outer joined record, you can add left outer joins criteria into the ON clause.
To create left outer joins criteria into the ON clause:
On the Criteria pageclick the Edit button to access the Edit Criteria Properties page.
On the Edit Criteria Properties page, select ON clause of outer join <Alias Name> from the This Criteria Belongs To drop-down list.
Click the OK button.