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

Using an Extract Override

You can create an extract override to override the Application Source Qualifier default query for PeopleSoft records. Use any SQL statement supported by the underlying database for the PeopleSoft source system. The PowerCenter Integration Service uses the extract override to extract data from connected sources.

Use an extract override for an Application Source Qualifier connected to PeopleSoft records only. The PowerCenter Integration Service does not perform extract overrides for Application Source Qualifiers connected to PeopleSoft trees. When you connect an Application Source Qualifier to a PeopleSoft tree, the Designer disables the Extract Override option.

When you create an extract override for PeopleSoft records, you can either:

Generate and edit the default query.
To use the existing transformation options in the extract override, generate and edit the default query. When the Designer generates the default query, it incorporates all other configured options, such as a filter or number of sorted ports. The resulting query overrides all other options you might subsequently configure in the transformation.

Manually enter the entire extract override.
The resulting query overrides all other options configured in the transformation.

For example, an Application Source Qualifier is already configured with the following join override:

PS_AB_CREDITBU_VW.CUST_ID = PS_AB_CREDITCP_VW.CORPORATE_CUST_ID AND PS_AB_CREDITBU_VW.CR_LIMIT = PS_AB_CREDITCP_VW.CR_LIMIT AND PS_AB_CREDITCP_VW.CR_LIMIT_RANGE > 100

When you generate the default query, the Designer incorporates the join override in the WHERE clause:

SELECT PS_AB_CREDITCP_VW.CORPORATE_SETID, PS_AB_CREDITCP_VW.CORPORATE_CUST_ID, PS_AB_CREDITCP_VW.NAME1, PS_AB_CREDITCP_VW.ROLEUSER, PS_AB_CREDITCP_VW.BAL_AMT, PS_AB_CREDITCP_VW.CR_LIMIT, PS_AB_CREDITCP_VW.CR_LIMIT_CORP, PS_AB_CREDITCP_VW.CR_LIMIT_REV_DT, PS_AB_CREDITCP_VW.CR_LIMIT_RANGE, PS_AB_CREDITCP_VW.CURRENCY_CD, PS_AB_CREDITBU_VW.SETID, PS_AB_CREDITBU_VW.CUST_ID, PS_AB_CREDITBU_VW.NAME1, PS_AB_CREDITBU_VW.ROLEUSER, PS_AB_CREDITBU_VW.BAL_AMT, PS_AB_CREDITBU_VW.CR_LIMIT, PS_AB_CREDITBU_VW.CR_LIMIT_REV_DT, PS_AB_CREDITBU_VW.CR_LIMIT_RANGE, PS_AB_CREDITBU_VW.CREDIT_CLASS, PS_AB_CREDITBU_VW.CURRENCY_CD 
FROM PS_AB_CREDITCP_VW, PS_AB_CREDITBU_VW 
WHERE
PS_AB_CREDITBU_VW.CUST_ID = PS_AB_CREDITCP_VW.CORPORATE_CUST_ID AND PS_AB_CREDITBU_VW.CR_LIMIT = PS_AB_CREDITCP_VW.CR_LIMIT AND PS_AB_CREDITCP_VW.CR_LIMIT_RANGE > 100

To use the join override in the extract override, you can generate the default query, and then edit the rest of the query, rather than entering the entire extract override.

Verify that the SQL statement syntax in the extract override is valid for the underlying database of your PeopleSoft system. For example, when you generate and edit the default query for an effective dated PeopleSoft record, you might need to edit the SQL statement. If you link the TO_EFFDT port to another transformation or target, the default query includes a NULL statement. If the underlying database is Oracle, for example, you need to change NULL to to_date(NULL).

When you enter an extract override on the Partitions tab of the session properties, you override the extract override in the Application Source Qualifier and on the Transformations tab of the session properties.

Rename Saved Search

Table of Contents

PowerExchange for PeopleSoft User Guide for PowerCenter

PowerExchange for PeopleSoft User Guide for PowerCenter

Using an Extract Override

Using an Extract Override