Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

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

Feedback

Lookup SQL Override Tips
Content

The lookup override provides a mechanism to bind the physical column of one or more lookup tables to the logical lookup ports.
The datatype of the physical column must be the same as that of the lookup port to which it is bound.
The lookup override is only supported when lookup caching is turned on.
The lookup override is used to prepare the SQL statement to build the lookup cache.

The lookup override syntax is different from a SQL override specified in the Source Qualifier.

The intent of the lookup override syntax is to remove the limitation of specifying the field (port) name in the same order as they appear in the transformation.

Example:

The syntax of the lookup override is as follows:

Suppose the lookup transformation has 2 lookup ports say L1 and L2.  Also suppose that the lookup table is LKUPTAB and the columns in this table are L1 and L2. The default lookup override will then be

SELECT LKUPTAB.L1 as L1, LKUPTAB.L2 as L2 from LKUPTAB

Note that because of the as clause one alter the lookup override will remain correct:

SELECT LKUPTAB.L2 as L2, LKUPTAB.L1 as L1 from LKUPTAB

Now suppose we want to join two tables LKUPTAB1 (COL1, key1)and LKUPTAB2 (COL2, key2) in the lookup override.
The two tables are related by key1 and key2.
The lookup override may look something like

SELECT LKUPTAB1.COL1 as L1, LKUPTAB.COL2 as L2 fromLKUPTAB1, LKUPTAB2
WHERE LKUPTAB1.key1 = LKUPTAB2.key2



More Information
  • Make sure after altering the default lookup override, the number of lookup ports specified in the lookup override is same as the number of the lookup ports in the lookup transformations. In other words, all lookup ports in the lookup transformation must appear in the lookup override.
  • The datatypes of the physical column must match to the datatype of the lookup port to which it is bound.
  • SQL functions can be used on a physical column in the lookup override.


Reference
PowerCenter Designer Guide


Attachments


Applies To
Product: PowerCenter
Problem Type:
User Type: Administrator
Project Phase:
Product Version: PowerCenter
Database:
Operating System:
Other Software:

Last Modified Date: 9/17/2009 5:52 AM ID: 11009
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)