PowerExchange for PeopleSoft User Guide for PowerCenter

10.4.0
- 10.5.8
- 10.5.7
- 10.5.6
- 10.5.4
- 10.5

Back Next

Entering a Join Override

You can create a custom join to override the default join in the Application Source Qualifier. In the join, you can enter any expression supported by the underlying database for the PeopleSoft source system. The Designer appends the join to existing WHERE clauses in the default query.

You might create a join override under the following conditions:

The names of key columns do not match.
For example, you might have one source with a key column in Source1 named EmployeeNum. You want to join the source with another with a non-key column in Source2 named EmployeeNumber. Since the column names differ, the Application Source Qualifier does not join them. You can enter the following join override:

Source1.EmployeeNum = Source2.EmployeeNumber

To perform an outer join
. For example, you enter a left outer join to pass all of Source1 and join Source2 with Source1 where the Social_Security columns are equal:

Source1.Social_Security(+) = Source2.Social_Security

You can define a join override using any column except the TO_EFFDT column of an effective dated PeopleSoft record.

To create a join override:

Create an Application Source Qualifier transformation containing data from multiple sources or associated sources.

Edit the Application Source Qualifier and select the Properties tab.

Click the right corner of the Join Override option to open the Source Editor.

Enter the syntax for the join.

Enter only statements supported by the underlying database for the PeopleSoft source system.

Use database tables names in the join syntax. Database table names for PeopleSoft record sources typically begin with PS_. For example, the database table name for the PeopleSoft record PROJ_MILE_TBL is PS_PROJ_MILE_TBL.

Select port names from the Ports tab to ensure the database table name is correct. You can define a join override using any port except the TO_EFFDT port of an effective dated PeopleSoft record.

Click OK to return to the Edit Transformations dialog box. Click OK again to return to the Designer.

The Application Source Qualifier adds the join override to the default query.

When you create a custom join, the Designer assumes the join is valid. If the join is not valid, the session fails. You can validate a join override.

Rename Saved Search

Table of Contents

PowerExchange for PeopleSoft User Guide for PowerCenter

PowerExchange for PeopleSoft User Guide for PowerCenter

Entering a Join Override

Entering a Join Override