Preface
The Transformation Language
Constants
Operators
Variables
Dates
Functions
- Function Categories
  - Aggregate Functions
  - Aggregate Functions and Nulls
    - Filter Conditions
  - Binary Functions
  - Character Functions
  - Conversion Functions
  - Data Cleansing Functions
  - Date Functions
  - Encoding Functions
  - Financial Functions
  - Numeric Functions
  - Scientific Functions
  - Special Functions
  - String Functions
  - Test Functions
  - Variable Functions
  - Window Functions
- ABORT
- ABS
- ADD_TO_DATE
- AES_DECRYPT
- AES_ENCRYPT
- ASCII
- AVG
- BINARY_COMPARE
- BINARY_CONCAT
- BINARY_LENGTH
- BINARY_SECTION
- CEIL
- CHOOSE
- CHR
- CHRCODE
- COMPRESS
- CONCAT
- CONVERT_BASE
- COS
- COSH
- COUNT
- CRC32
- CUME
- DATE_COMPARE
- DATE_DIFF
- DEC_BASE64
- DEC_HEX
- DECODE
- DECOMPRESS
- EBCDIC_ISO88591
- ENC_BASE64
- ENC_HEX
- ERROR
- EXP
- FIRST
- FLOOR
- FV
- GET_DATE_PART
- GREATEST
- IIF
- IN
- INDEXOF
- INITCAP
- INSTR
- ISNULL
- IS_DATE
- IS_NUMBER
- IS_SPACES
- LAG
- LAST
- LAST_DAY
- LEAD
- LEAST
- LENGTH
- LN
- LOG
- LOOKUP
- LOWER
- LPAD
- LTRIM
- MAKE_DATE_TIME
- MAX (Dates)
- MAX (Numbers)
- MAX (String)
- MD5
- MEDIAN
- METAPHONE
- MIN (Dates)
- MIN (Numbers)
- MIN (String)
- MOD
- MOVINGAVG
- MOVINGSUM
- NPER
- PERCENTILE
- PMT
- POWER
- PV
- RAND
- RATE
- REG_EXTRACT
- REG_MATCH
- REG_REPLACE
- REPLACECHR
- REPLACESTR
- REVERSE
- ROUND (Dates)
- ROUND (Numbers)
- RPAD
- RTRIM
- SETCOUNTVARIABLE
- SET_DATE_PART
- SETMAXVARIABLE
- SETMINVARIABLE
- SETVARIABLE
- SHA256
- SIGN
- SIN
- SINH
- SOUNDEX
- SQRT
- STDDEV
- SUBSTR
- SUM
- SYSTIMESTAMP
- TAN
- TANH
- TIME_RANGE
- TO_BIGINT
- TO_CHAR (Dates)
- TO_CHAR (Numbers)
- TO_DATE
- TO_DECIMAL
- TO_FLOAT
- TO_INTEGER
- TRUNC (Dates)
- TRUNC (Numbers)
- UPPER
- VARIANCE
Creating Custom Functions
Custom Function API Reference

Transformation Language Reference

10.5.7
- 10.5.6
- 10.5.1
- 10.5
- 10.4.0

Back Next

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)

Rename Saved Search

Table of Contents

Transformation Language Reference

Transformation Language Reference

LOOKUP

LOOKUP

Using the Lookup Transformation or the LOOKUP Function

Syntax

Return Value

Example

Tips for LOOKUP