PowerExchange for PeopleSoft User Guide for PowerCenter

10.5.6
- 10.5.8
- 10.5.7
- 10.5.4
- 10.5
- 10.4.0

Back Next

Entering the Lookup SQL Override

When you create a Lookup transformation to access XLATTABLE data, enter a lookup SQL override to filter out unrelated XLATTABLE data. Before you create a lookup SQL override, connect transformation output ports to the rest of the mapping. The Designer uses these connections to generate the default lookup SQL statement.

To enter a lookup SQL override, generate the default SQL. Then, add a WHERE clause to the end of the default statement. The clause must contain the following:

WHERE FIELDNAME = ‘[INPUT_PORT_NAME]’ AND LANGUAGE_CD = ‘[LANGUAGE_CODE]’

In the first condition, INPUT_PORT_NAME is the name of the input port used in the lookup condition. This port contains the codes you want to describe. Use this clause to ensure the PowerCenter Integration Service returns descriptions for the codes used in the mapping.

In the second condition, enter the PeopleSoft language code. This determines the language in which the PowerCenter Integration Service extracts language-sensitive data. This language code must be identical to the language code used in the session database connection. If you do not enter a language code in the session database connection, the language code in the lookup SQL override must correspond to the language of the PeopleSoft record base table.

If necessary, you can add additional conditions, such as:

EFF_STATUS = ‘[ROW_EFFECTIVE_STATUS]’ AND EFFDT = [ROW_EFFECTIVE_DATE]

Use any SQL statements supported by the underlying database of the PeopleSoft system.

The Designer generates the following SQL statement for the Lookup transformation LKP_XLATTABLE:

SELECT XLATTABLE.FIELDNAME as FIELDNAME, XLATTABLE.LANGUAGE_CD as LANGUAGE_CD, XLATTABLE.EFFDT as EFFDT, XLATTABLE.VERSION as VERSION, XLATTABLE.EFF_STATUS as EFF_STATUS, XLATTABLE.XLATLONGNAME as XLATLONGNAME, XLATTABLE.XLATSHORTNAME as XLATSHORTNAME, XLATTABLE.FIELDVALUE as FIELDVALUE FROM XLATTABLE

You can then add a WHERE clause with additional conditions as follows:

WHERE FIELDNAME = ‘CUST_STATUS’ AND LANGUAGE_CD = ‘ENG’ AND EFF_STATUS = ‘A’ 

AND EFFDT = (SELECT MAX(EFFDT) FROM XLATTABLE SECONDARY_NAME WHERE 

FIELDNAME = ‘CUST_STATUS’ AND EFF_STATUS = ‘A’ AND LANGUAGE_CD = ‘ENG’ AND 

SECONDARY_NAME.FIELDVALUE = XLATTABLE.FIELDVALUE)

This clause ensures the PowerCenter Integration Service returns values from the XLATTABLE table where the FIELDNAME is “CUST_STATUS,” the language is English, the status is active, and the effective date is the most recent. This statement uses “SECONDARY_NAME” as a secondary reference for the XLATTABLE table to allow a self-join.

Rename Saved Search

Table of Contents

PowerExchange for PeopleSoft User Guide for PowerCenter

PowerExchange for PeopleSoft User Guide for PowerCenter

Entering the Lookup SQL Override

Entering the Lookup SQL Override