Optional properties

Specify a Default Value to assign to the value attribute, if a key value was not found in the lookup table.

If Use Key Value is activated, the key value will be assigned to the value attribute instead of the default, if the lookup does not find a match.

If activated, the lookup will also be performed for empty or NULL key values; otherwise, the selected default method applies.

Enter the estimated number of lookup records to optimize memory allocation and lookup performance.

The entire result set is loaded into cache memory and remains unchanged until the transformation process finishes. Choosing an appropriate value for Lookup Size allows allocating memory in one piece while a low value will cause the program to allocate memory incrementally.

For example, assume that you want to replace the product number used for German products by the product number used in the U.S. The German products are in the table PRODUKTE(PR_NUMMER, PR_NAME, PR_PREIS). The IN-Port of the DB Lookup component contains those three attributes.

The table to perform the lookup of the U.S. product number is table LOOKUP_PRODUCTS(SOURCE, DESTINATION). The SOURCE column contains the German product numbers and the DESTINATION column contains the U.S. product number.

If no value for the German PR_NUMMER can be found in the LOOKUP_PRODUCTS, the current PR_NUMMER will be replaced by the string “INVALID”. A successful lookup will replace the German product number by the corresponding U.S. number.

To set up the DB Lookup Component for this example, choose:

• Key Attribute: IN.PR_NUMMER

• Value Attribute: IN.PR_NUMMER

• Default Value: “INVALID”

• Query: SELECT SOURCE, DESTINATION FROM LOOKUP_PRODUCTS