Slowly Changing Dimensions
Test Cases for SCD Type 2
August 16, 2017
Threads
What happens when the session/mapping is triggered?
August 23, 2017
Show all

ORDER BY Override in Lookup Transformation

Lookup Transformation

Lookup Transformation

For any mapping design, if we have a requirement to use the Lookup transformation, an ORDER BY clause is generated by the PowerCenter Integration Service by default when the session is executed or during run time.

If you need to stop the default ORDER BY you can override it.

Once the SQL override is used to 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. (Example shown below).

There will also be a need to append the ORDER BY based on your need instead of overriding it, in those cases, enter an ORDER BY statement that contains the condition ports in the same order they appear in the Lookup condition.

Of course there are some conditions which it should meet:

  • The SQL override (when generated) requires the lookup to be cached. Un-cached lookup 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

Let’s take a Look up transformation which needs to use 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

Here are the limitations:

  • This is not supported on DB2 databases. The comment notation is not valid in DB2.
  • If you use push-down 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.

All this information can be found in the PowerCenter Transformation Guide > “Lookup Transformation” > “Lookup Query”

Sid
Sid
Business Intelligence Consultant and Trainer with 13+ years of extensive work experience on various client engagements. Delivered many large data warehousing projects and trained numerous professionals on business intelligence technologies. Extensively worked on all facets of data warehousing including requirement gathering, gap analysis, database design, data integration, data modeling, enterprise reporting, data analytics, data quality, data visualization, OLAP. Has worked on broad range of business verticals and hold exceptional expertise on various ETL tools like Informatica Powercenter, SSIS, ODI and IDQ, Data Virtualization, DVO, MDM.

Leave a Reply

Your email address will not be published. Required fields are marked *

Share
+1
Tweet
Pin
Share