Required properties

Select a Key Attribute from the list of IN-Port attributes. This attribute corresponds to the first column of the lookup table.

Select the attribute to receive the value returned by the lookup from the Value attribute list. The lookup value returned will overwrite any existing value.

Both Key Attribute and Value Attribute might refer to the same attribute of the record structure therefore allowing overwriting a key with its corresponding value.

To open the Query window, click Query and enter the query for the lookup.

The Lookup is made of a SELECT query returning a single value that corresponds with the Value Attribute.

The query required in the DB Lookup Dynamic component is slightly different from the one used in the DB Lookup component. The query will return exactly one single value, which is the value found for the corresponding Key Attribute. The value of the Key Attribute is represented in a predefined variable named LOOKUP, which is used as a placeholder in the WHERE clause of the query. The notation for this placeholder is an SBN expression.

The basic structure of the query for the DB Lookup Dynamic component is:

SELECT <value attribute>
FROM <lookup table>
WHERE <key attribute> = '[LOOKUP]'

During execution of the query, the LOOKUP will be replaced by the current value of the Key Attribute of the current record and the SBN will be evaluated. If the Key Attribute is a character datatype, use quotes: '[LOOKUP]'. You can use functions to apply formatting or calculations:

SELECT DESTINATION
FROM LOOKUP_PRODUCTS
WHERE SOURCE = '[uRTrim(LOOKUP)]'
Optional Properties
Default Value

Specify a Default Value to assign to the value attribute, in case the key value is 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 fails.

If activated, the lookup is performed even for empty or NULL key values. Otherwise the selected default method applies.

For example, assume 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). Your IN-Port at DB LOOKUP component therefore contains those three attributes.

The table to lookup the US 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 Dynamic component for this example, choose:

• Key Attribute: IN.PR_NUMMER

• Value Attribute: IN.PR_NUMMER

• Default Value: ‘INVALID’

• Query: SELECT DESTINATION FROM LOOKUP_PRODUCTS, where SOURCE = ‘[LOOKUP]’