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:
Generate the SQL in the Lookup SQL Override.
Append two dashes (--) to comment out the ORDER BY clause right at the end of the SQL statement.
Enter an ORDER BY statement that contains the condition ports in the same order they appear in the Lookup condition.
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 --
"CMN_1701 Data for Lookup fetched from the database is not sorted on condition ports" running a PowerCenter session with a SQL Override in a Lookup transformation (10005)
What can we do to improve this information (2000 or fewer characters)