Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

(4 Ratings)
facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

HOW TO: Override the Lookup ORDER BY clause generated by the PowerCenter session
Solution

By default, for every Lookup transformation, an ORDER BY clause is generated by the PowerCenter Integration Service when the session is executed.

To override this ORDER BY clause do the following:

  1. Generate the SQL in the Lookup SQL Override.

  2. Append two dashes (--) to comment out the ORDER BY clause right at the end of the SQL statement.

  3. Enter an ORDER BY statement that contains the condition ports in the same order they appear in the Lookup condition.

Conditions

  • Generating a SQL override requires the lookup to be cached.  Uncached lookups are not supported with a SQL override
  • The custom ORDER BY clause must include the Lookup condition ports and these columns must precede any other columns in the ORDER BY clause.
  • When you enter the custom ORDER BY statement, enter the columns in the same order as the ports in the lookup condition.
  • If you override the lookup query with an ORDER BY statement without adding comment notation, the lookup fails.
  • You must also enclose all database reserved words in quotes.

Example

A Lookup transformation uses the following lookup condition:

ITEM_ID = IN_ITEM_ID
PRICE <= IN_PRICE

The Lookup transformation includes three lookup ports used in the mapping, ITEM_ID, ITEM_NAME, and PRICE. When you enter the ORDER BY clause, enter the columns in the same order as the ports in the lookup condition. You must also enclose all database reserved words in quotes. Enter the following lookup query in the lookup SQL override:

SELECT ITEMS_DIM.ITEM_NAME, ITEMS_DIM.PRICE, ITEMS_DIM.ITEM_ID FROM ITEMS_DIM ORDER BY ITEMS_DIM.ITEM_ID, ITEMS_DIM.PRICE --

NOTE

  • This is not supported on DB2 databases.  The comment notation is not valid in DB2.
  • If you use pushdown optimization, you cannot override the ORDER BY clause or suppress the generated ORDER BY clause with a comment notation.
  • Sybase has a 16 column ORDER BY limitation. If the Lookup transformation has more than 16 lookup/output ports including the ports in the lookup condition, override the ORDER BY clause or use multiple Lookup transformations to query the lookup table.
More Information
Reference
PowerCenter Transformation Guide > "Lookup Transformation" > "Lookup Query"
Applies To
Product: PowerCenter
Problem Type:
User Type: Administrator
Project Phase:
Product Version: PowerCenter 8.x; PowerCenter 9.x
Database:
Operating System:
Other Software:
Attachments
Last Modified Date:3/19/2013 9:31 PMID:10065
People who viewed this also viewed

Feedback

Did this KB document help you?



What can we do to improve this information (2000 or fewer characters)