Lookup Condition
The Data Integration Service looks up data in the lookup source based on a lookup condition. When you configure a lookup condition in a Lookup transformation, you compare the value of one or more columns in the source data with values in the lookup source or cache.
For example, the source data contains an employee_number. The lookup source table contains employee_ID, first_name, and last_name. You configure the following lookup condition:
employee_ID = employee_number
For each employee_number, the Data Integration Service returns the employee_ID, last_name, and first_name column from the lookup source.
The Data Integration Service can return more than one row from the lookup source. You configure the following lookup condition:
employee_ID > employee_number
The Data Integration Service returns rows for all employee_ID numbers greater than the source employee number.
Null Values in a Data Object Lookup
When an input to the Lookup condition is NULL, a data object Lookup transformation returns a single row with null values for output-only ports, and values from input rows for pass-through ports.
For example, the following lookup condition performs a lookup on a data source that contains one or more rows where the value for employee_ID is NULL:
employee_ID = employee_number
In this example, you use a lookup table with the following data:
EMPLOYEE_ID | LAST_NAME |
|---|
1294765 | Hara |
1356356 | Carver |
1407207 | NULL |
1570348 | Draper |
NULL | Limonov |
You compare the following input values from your data source with the lookup table:
EMPLOYEE_NUMBER
---------------
1294765
1356356
1407207
1648246
NULL
In this example, the Lookup condition produces the following results:
1294765,Hara
1356356,Carver
1407207,NULL
NULL,NULL
NULL,NULL
The Lookup condition finds matches between EMPLOYEE_ID and EMPLOYEE_NUMBER for the first two rows. For the third row, the lookup source contains a row with a NULL value that does not participate in the lookup condition. It matches the lookup condition, and returns a result with the NULL value for the non-lookup column.
For the fourth and fifth rows, the Lookup condition does not find a match, and returns NULL for both values. For the fifth row, note that the Lookup condition does not find a match again, because NULL does not match anything, including NULL.
Configure the Lookup Condition
The Lookup condition is an expression that determines which rows to retrieve from the lookup source. Configure the lookup condition on the Lookup tab of the Properties view.
The following image shows a lookup condition to perform a lookup by customer number:
You can configure the following options on the Lookup tab:
- Specify by
- Choose Value to select the lookup column and input port names. Select Parameter to configure an expression parameter to define the lookup condition.
- Lookup column
- The column in the lookup source to match with a column from the input row. You can include multiple columns in the lookup condition.
- Operator
- The operator that determines the condition to search for between the lookup column and the input port. Operators include =, !=, >, < , >=, <=.
- Input Port
- The input port that contains the value to search for in the lookup source. You can compare more than one input port to ports in the lookup source.
Rules and Guidelines for Lookup Transformation Conditions
Certain rules and guidelines apply when you enter a condition for a Lookup transformation.
Consider the following rules and guidelines when you enter a condition for a Lookup transformation.
- •The datatypes for the columns in a lookup condition must match.
- •Use one input port for each lookup port in the lookup condition. You can use the same input port in more than one condition in a transformation.
- •If you use a port selector or a dynamic port in a lookup condition, the lookup condition considers all the ports in the expression.
- •You can use a dynamic input port or a port selector as the input port of a lookup condition. The number of generated ports in the input port must be equal to the number of ports in the lookup column.
- •When processing a Lookup transformation with multiple lookup conditions, the Integration Service returns rows that match all the lookup conditions.
- •You can create an expression parameter to parameterize the lookup condition in a nonreusable lookup transformation.
- •To increase lookup performance, enter conditions in the following order:
- - Equal to (=)
- - Less than (<), greater than (>), less than or equal to (<=), greater than or equal to (>=)
- - Not equal to (!=)
- •Use one of the following operators when you create a lookup condition: =, >, <, >=, <=, or !=.
- •The Integration Service processes lookup matches differently based on whether you configure the Lookup transformation with a dynamic lookup cache, static lookup cache, or uncached lookup.
- •The Integration Service matches null values. For example, the Integration Service considers a lookup port and an input port equal if they both have null values.
- • If the columns in a lookup condition are Decimal data types, the precision of each column must belong to the same precision range. Valid precision ranges include:
- - Decimal 0-18
- - Decimal 19-28
- - Decimal 29-38
- - Decimal 39 and over
For example, if you define the condition DecimalA = DecimalB where DecimalA has precision 15 and DecimalB has precision 25, the lookup condition is not valid.
