PowerExchange for PeopleSoft User Guide for PowerCenter

10.5.4
- 10.5.8
- 10.5.7
- 10.5.6
- 10.5
- 10.4.0

Back Next

Joining Effective Dated PeopleSoft Records

You can join multiple effective dated PeopleSoft records in an Application Source Qualifier transformation. The PowerCenter Integration Service joins effective dated PeopleSoft records in different ways, depending on the transformation options you choose.

The Application Source Qualifier includes three transformation options for effective dated PeopleSoft records. These options only appear if you connect one or more effective dated records to the Application Source Qualifier.

The following table lists the transformation options in the Application Source Qualifier for effective dated PeopleSoft records:

Application Source Qualifier Option	Description
Extract Current Rows	Extracts the current rows from effective dated records. Uses the date in Extract Date as the current date. When you use this option, the Designer modifies the default query and default join to select only the most recent data as defined by the extract date.
Extract Date	Defines the current date to use with the Extract Current Rows option. Uses the current date when this attribute is empty. Enter the date in the following format: MM/DD/YYYY HH24:MI:SS .
Effective Date Join Order	Specifies the join order between effective dated records. This option only appears when you connect multiple effective dated records to the Application Source Qualifier.

By default the PowerCenter Integration Service joins all rows from all records for each key in an effective dated record. However, you can join certain rows based on their effective dates and effective sequences by defining some of the options listed above.

When you specify to extract current rows, the PowerCenter Integration Service returns zero or one row for each key. When you connect only one record to the Application Source Qualifier, the PowerCenter Integration Service extracts the current row for each key in that record. When you connect multiple records to the Application Source Qualifier, you get different results. The results vary according to what you specify in the Effective Date Join Order option.

Use the Effective Date Join Order option to specify a join order for effective dated PeopleSoft records. The join order you specify determines which rows from each record the PowerCenter Integration Service joins. When you enter a join order, the PowerCenter Integration Service joins the row from the second table with a maximum effective date that is less than or equal to
the effective date of the row from the first table.

You can specify a join order in the Application Source Qualifier in the following ways:

Manually enter the PeopleSoft record names in the Effective Date Join Order field. Separate each record name with a comma.

Click the arrow in the Effective Date Join Order field to open the Effective Date Join Order dialog box. Use the arrows in the dialog box to move the record names into the order and click OK.

The following table describes how the PowerCenter Integration Service extracts rows from effective dated PeopleSoft records according to the transformation options you define:

Number of Effective Dated Records in the Application Source Qualifier	Extract Current Rows Specified	Effective Date Join Order Specified	PowerCenter Integration Service Results
One	No	-	Ignores Extract Date. Returns all rows.
One	Yes	-	Uses Extract Date. Returns current rows for each key.
Multiple	Yes	Yes	Uses Extract Date. For each key, joins effective dated records based on the join order specified.
Multiple	Yes	No	Uses Extract Date. For each key, joins current rows from all records.
Multiple	No	Yes	Ignores Extract Date. For each key, joins effective dated records based on the join order specified.
Multiple	No	No	Ignores Extract Date. For each key, joins all rows from all records.