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

Adding the Owner Name

When you import the XLATTABLE table using a database username that is not
the owner of the table, enter the table owner name in the lookup SQL override. When you enter the table owner name to the SQL statement, you define the XLATTABLE table as “ownername
.XLATTABLE.” To ensure the lookup SQL override remains valid, you must also rename the table XLATTABLE.

For example, the owner of the XLATTABLE table used in the Lookup transformation LKP_XLATTABLE is jdoe
. You import the table using a different database username.

When you enter the lookup SQL override, first generate the default SQL:

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

To enter the XLATTABLE table owner name and rename the table XLATTABLE, edit the table name as follows:

FROM jdoe.XLATTABLE XLATTABLE

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

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

EFFDT = (SELECT MAX(EFFDT) FROM jdoe.XLATTABLE SECONDARY_NAME WHERE 

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

SECONDARY_NAME.FIELDVALUE = XLATTABLE.FIELDVALUE)

As in the previous example, 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. It also includes the table owner name “jdoe.XLATTABLE.”

The complete lookup SQL override for the Lookup transformation is as follows:

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 jdoe.XLATTABLE XLATTABLE WHERE FIELDNAME = ‘CUST_STATUS’ AND LANGUAGE_CD = ‘ENG’ AND EFF_STATUS = ‘A’ AND 

EFFDT = (SELECT MAX(EFFDT) FROM jdoe.XLATTABLE SECONDARY_NAME WHERE 

FIELDNAME = ‘CUST_STATUS’ AND EFF_STATUS = ‘A’ AND LANGUAGE_CD = ‘ENG’ AND SECONDARY_NAME.FIELDVALUE = XLATTABLE.FIELDVALUE)

Rename Saved Search

Table of Contents

PowerExchange for PeopleSoft User Guide for PowerCenter

PowerExchange for PeopleSoft User Guide for PowerCenter

Adding the Owner Name

Adding the Owner Name