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

Linking the TO_EFFDT Port

The Designer adds a new column, TO_EFFDT, to the source definition when you import an effective dated PeopleSoft record. The Designer determines the value of TO_EFFDT by the next value of EFFDT for the same key.

When you link TO_EFFDT to another transformation or target, the default query includes a self join to determine the value of TO_EFFDT. When there are no future rows for the same key, the Designer assigns NULL to TO_EFFDT.

The TO_EFFDT port in the Application Source Qualifier is linked to a port in another transformation and ultimately to the target. The following SQL statement is the default query for this Application Source Qualifier:

SELECT PS_EMP_SAL.EFFDT, PS_EMP_SAL.EMP_ID, PS_EMP_SAL.EMP_SAL, A0.EFFDT FROM PS_EMP_SAL, PS_EMP_SAL A0 WHERE PS_EMP_SAL.EMP_ID = A0.EMP_ID AND A0.EFFDT IN (SELECT MIN(EFFDT) FROM PS_EMP_SAL A1 WHERE A1.EMP_ID = PS_EMP_SAL.EMP_ID AND A1.EFFDT > PS_EMP_SAL.EFFDT)UNION ALL SELECT PS_EMP_SAL.EFFDT, PS_EMP_SAL.EMP_ID, PS_EMP_SAL.EMP_SAL, NULL
FROM PS_EMP_SAL WHERE PS_EMP_SAL.EFFDT IN (SELECT MAX(EFFDT) FROM PS_EMP_SAL A0 
WHERE PS_EMP_SAL.EMP_ID = A0.EMP_ID)

The SQL statement above includes a self join. It selects a value for the TO_EFFDT port by selecting a different EFFDT value from the same table. The WHERE clause determines the particular EFFDT value it selects. If the PowerCenter Integration Service finds a row with the same key value with a greater value in EFFDT, it selects the greater EFFDT value as the value for TO_EFFDT.

The SQL statement includes a UNION ALL statement. After the UNION ALL statement, the query selects NULL for the TO_EFFDT port. It selects NULL for rows where the EFFDT value is the greatest in the table for the same key.

The following table shows an example of the data in the EMP_SAL table:

EFFDT	EMP_ID	SALARY
1990-01-01 00:00:00.000	1000	35,000.00
1992-01-01 00:00:00.000	1000	45,000.00
1994-01-01 00:00:00.000	1000	70,000.00
1990-01-01 00:00:00.000	9999	50,000.00

The following table displays the data when the PowerCenter Integration Service extracts data from this table and creates values for the TO_EFFDT port:

EFFDT	EMP_ID	SALARY	TO_EFFDT
1990-01-01 00:00:00.000	1000	35,000.00	1992-01-01 00:00:00.000
1992-01-01 00:00:00.000	1000	45,000.00	1994-01-01 00:00:00.000
1994-01-01 00:00:00.000	1000	70,000.00	NULL
1990-01-01 00:00:00.000	9999	50,000.00	NULL

Rename Saved Search

Table of Contents

PowerExchange for PeopleSoft User Guide for PowerCenter

PowerExchange for PeopleSoft User Guide for PowerCenter

Linking the TO_EFFDT Port

Linking the TO_EFFDT Port