PowerExchange for PeopleSoft User Guide for PowerCenter

10.5
- 10.5.8
- 10.5.7
- 10.5.6
- 10.5.4
- 10.4.0

Back Next

Joining Records

When you connect multiple PeopleSoft record source definitions to one Application Source Qualifier, the Designer creates the default join based on logical key column and column name relationships. The Designer can create a default join of PeopleSoft records when both of the following conditions are true:

Source columns have matching names.

At least one matching-name column is a PeopleSoft key.

The Designer also creates a default join of PeopleSoft records if you create primary-foreign key relationships in the Source Analyzer.

You can, therefore, join two records in the following cases:

Sources have PeopleSoft key columns with matching names.
The Designer joins two key columns if they have matching names. Columns must be PeopleSoft keys.

For example, you connect two PeopleSoft sources to an Application Source Qualifier. Both sources have a PeopleSoft key column named EmployeeNumber. The Designer joins the two columns.

A PeopleSoft key column name matches a non-key column name.
The Designer joins two columns when a PeopleSoft key column name in one source matches a non-key column name in another source.

For example, if one of the connected sources has a PeopleSoft key column named EmployeeNumber and another source has a column of the same name marked not-a-key, the Designer joins the two columns.

Sources have a user-defined primary-foreign key relationship.
The Designer joins two key columns if they have a primary-foreign key relationship.

To join sources, you connect source columns to one Application Source Qualifier. The default join is an inner equijoin. The PowerCenter Integration Service generates a SELECT statement that includes columns used for the join in the WHERE clause:

Source1.Column_Name = Source2.Column_Name AND 
Source1.Column_Name = Source2.Column_Name...

If the default join does not join source tables the way you want, you can enter a join override in the Application Source Qualifier.

By default, the PowerCenter Integration Service joins all rows from all PeopleSoft records. When you connect one or more effective dated records to an Application Source Qualifier, you can alter the default join by selecting certain transformation options.

Rename Saved Search

Table of Contents

PowerExchange for PeopleSoft User Guide for PowerCenter

PowerExchange for PeopleSoft User Guide for PowerCenter

Joining Records

Joining Records