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

Joining Effective Dated Records with a Join Order

The following example shows how the PowerCenter Integration Service joins two effective dated PeopleSoft records when you define the following properties:

Extract Current Rows = No

Join Order = Salary_data, Title_data

When you do not extract current rows and define the join order, the PowerCenter Integration Service performs the following tasks:

It ignores the value in Extract Date.

It selects a row from the first table.

It uses the current effective date from the row of the first table as the current date for the second table.

It joins the row with the current effective date from the second table with the row from the first table.

It repeats the steps for all rows in the first table.

The PowerCenter Integration Service works similarly when you join more than two effective dated PeopleSoft records. The current effective date for the row in the second table becomes the current date for the third table in the join order.

The following figure shows how the PowerCenter Integration Service joins the Salary_data and Title_data tables, where the current date is June 30, 2004:

The PowerCenter Integration Service ignores the current date. It selects each row from the Salary_data table and joins each one with a row from the Title_data table. It joins the row from Salary_data with a row from Title_data using the following condition:

Title_data.EFFDT <= Salary_data.EFFDT

The following table shows the rows that the PowerCenter Integration Service returns when it the Salary_data and Title_data tables:

EMP_ID	SALARY	TITLE
1000	45,000.00	Associate
1000	52,000.00	Associate
1000	65,000.00	Sr. Associate

Rename Saved Search

Table of Contents

PowerExchange for PeopleSoft User Guide for PowerCenter

PowerExchange for PeopleSoft User Guide for PowerCenter

Joining Effective Dated Records with a Join Order

Joining Effective Dated Records with a Join Order