Creating Left Outer Joins

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:

  1. Access the Query Manager search page by selecting Reporting Tools, and then Query, and then Query Manager.

  2. Click the Create New Query link, add a record, and select fields.

    Alternatively, open an existing query.

  3. Access the Records page.

  4. Find the joining record and then click the Join Record link on the same row as that record.

  5. 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.

  6. 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.

  7. 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.

  8. 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.

  9. 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.

  10. (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.

    See Viewing and Editing Query Properties.

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:

  1. On the Criteria page (Reporting Tools, and then Query, and then Query Manager, and then Criteria)click the Edit button to access the Edit Criteria Properties page.

  2. On the Edit Criteria Properties page, select ON clause of outer join <Alias Name> from the This Criteria Belongs To drop-down list.

  3. Click the OK button.