Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Dates
  6. Functions
  7. Creating Custom Functions
  8. Custom Function API Reference
  9. POWERCENTERHELP

Transformation Language Reference

Transformation Language Reference

LOOKUP

LOOKUP

Searches for a value in a lookup source column.
The LOOKUP function compares data in a lookup source to a value you specify. When the
PowerCenter Integration Service
finds the search value in the lookup table, it returns the value from a specified column in the same row in the lookup table.
When you create a session based on a mapping that uses the LOOKUP function, you must specify the database connections for $Source Connection Value and $Target Connection Value in the session properties. To validate a lookup function in an Expression transformation, verify that the lookup definition is in the mapping.
This function is not supported in mapplets.

Using the Lookup Transformation or the LOOKUP Function

Use the Lookup
transformation
rather than the LOOKUP
function
to look up values in PowerCenter mappings. If you use the LOOKUP function in a mapping, you need to enable the lookup caching option for 3.5 compatibility in the session properties. This option exists expressly for PowerMart 3.5 users who want to continue using the LOOKUP function, rather than creating Lookup transformations. For more information, see “Lookup Transformation” in the
PowerCenter Transformation Guide
.
You can define multiple searches for one lookup table within a LOOKUP function. However, each search must find a matching value to return the lookup value.

Syntax

LOOKUP(
result, search1, value1 [, search2, value2]
... )
The following table describes the arguments for this command:
Argument
Required/
Optional
Description
result
Required
Any datatype except Binary. Must be an output port in the same lookup table as search. Specifies the return value if the search matches the value. Always preface this argument with the reference qualifier :TD.
search1
Required
Datatype that matches the
value1
. Must be an output port in the same lookup table as result. Specifies the values you want to match to value. Always preface this argument with the reference qualifier :TD.
value1
Required
Any datatype except Binary. Must match
search1
datatype. The values you want to search for in the lookup source column specified in
search1
. You can enter any valid transformation expression.

Return Value

Result
if all searches find matching values. If the
PowerCenter Integration Service
finds matching values, it returns the result from the same row as the
search1
argument.
NULL if the search does not find any matching values.
Error if the search finds more than one matching value.

Example

The following expression searches the lookup source :TD.SALES for a specific item ID and price, and returns the item name if both searches find a match:
LOOKUP( :TD.SALES.ITEM_NAME, :TD.SALES.ITEM_ID, 10, :TD.SALES.PRICE, 15.99 )
ITEM_NAME
ITEM_ID
PRICE
Regulator
5
100.00
Flashlight
10
15.99
Halogen Flashlight
15
15.99
NULL
20
15.99
RETURN VALUE:
Flashlight

Tips for LOOKUP

When you compare char and varchar values, the LOOKUP function returns a result only if the two rows match. This means that both the value and the length for each row must match. If the lookup source is a padded char value of a specified length and the lookup search is a varchar value, you need to use the RTRIM function to trim trailing blanks from the lookup source so that the values match the lookup search:
LOOKUP(:TD.ORDERS.PRICE, :TD.ORDERS.ITEM, RTRIM( ORDERS.ITEM, ' '))
Use the :TD reference qualifier in the
result
and
search
arguments of a LOOKUP function:
LOOKUP(:TD.ORDERS.ITEM, :TD.ORDERS.PRICE, ORDERS.PRICE, :TD.ORDERS.QTY, ORDERS.QTY)