Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

FAQ: Can a non-input port be used in the Lookup Source Filter property?
Answer

Any valid WHERE clause can be added to the Lookup Source Filter property including ports that are not input ports in the Lookup transformation.

The Lookup Source Filter property restricts the number of rows returned from the database to the Lookup Cache based not only on the value of data in the Lookup transformation input port but based on any port.


More Information

The Lookup Source Filter property can be used to restrict the number of rows returned from the database to the Lookup Cache (Both Index as well as Data Cache). After completing pre-session tasks successfully, the lookup Cache Build up will start. This cache build up will be based on an SQL Query from the lookup transformation.

For example, a lookup transformation, m_lkp_ods_customer, in a mapping returns the following message in the session log:

2008-10-16 11:28:41: INFO: (31358 | LKPDP_1): (IS | IS_nbijilya): node02_nbijilya: DBG_21097: Lookup Transformation [m_lkp_ods_customer]: Default sql to create lookup cache: SELECT ODS_PRODUCT.PRODUCT_CODE FROM ODS_PRODUCT.

Now, if you want to restrict the lookup cache to only those rows from the database whose VENDOR_ID >=4020 for lookup, and the VENDOR_ID is not a lookup input port you can use lookup source filter property. The 'where' condition in the SQL query can be given directly in the Lookup Source Filter.
Once you give a condition like this, the SQL Query issued to initialize the Cache will be:

2008-10-16 14:39:45: INFO: (26202 | LKPDP_1): (IS | IS_nbijilya): node02_nbijilya: DBG_21097: Lookup Transformation [m_lkp_ods_customer]: Default sql to create lookup cache: SELECT PRODUCT_CODE FROM ODS_PRODUCT WHERE ODS_PRODUCT.VENDOR_ID >=4020.

Hence, it is very clear that the property restricts the lookups not only based on the value of data on the Lookup transformation input port but based on any port.


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

Reference
This may be unclear in documentation and will be clarified in a future release.

Attachments

Last Modified Date:11/6/2008 11:25 AMID:101781
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)