When you configure a data transfer task, you can add a second source to use as a lookup source. Configure the lookup source on the Second Source page.
The task queries the lookup source based on the lookup condition that you specify and returns the result of the lookup to the target.
Select a second source when you want to augment the source data with a related value or values from the lookup source. For example, the source is an orders table that contains a customer ID field. You might retrieve the customer name and address from the lookup source so that you can include them in the target object. The task returns all fields from the lookup source.
To optimize performance, the task caches the lookup source. The cache remains static and does not change as the task runs. The task deletes the cache files after the task completes.
You can preview the data in the lookup source. The preview returns the first 10 rows. To preview rows in alphabetical order, select Display fields in alphabetical order. Data Integration does not change the order of rows in the actual source. You can also download the preview results to a CSV file.
You can also filter the data from both sources before writing it to the target.
Lookup condition
When you select a second source to use as a lookup source, you must configure one or more lookup conditions.
A lookup condition defines when the lookup returns values from the lookup source. When you configure a lookup condition, you compare the value of one or more fields from the original source with values in the lookup source.
A lookup condition includes an incoming field from the original source, a field from the lookup source, and an operator. To avoid possible naming conflicts, the data transfer task applies the prefix SRC_ to the fields from the original source. If this results in a naming conflict for any field from the original source, the task applies the prefix IN_SRC_ to the field from the original source.
For example, you might configure the following lookup condition when the original source contains the CustID field, the lookup source contains the CustomerID field, and you want to return values from the lookup source when the customer IDs match:
Lookup Field
Operator
Incoming Field
CustomerID
Equals
SRC_CustID
You can use the following operators in a lookup condition:
•Equals
•Not Equals
•Less Than
•Less Than or Equals
•Greater Than
•Greater Than or Equals
When you enter multiple conditions, the task evaluates the lookup conditions using the AND logical operator to join the conditions. It returns rows that match all the lookup conditions.
When you include multiple conditions, to optimize performance, enter the conditions in the following order:
1Equals
2Less Than, Less Than or Equals, Greater Than, Greater Than or Equals
3Not Equals
The lookup condition matches null values. When an input field is NULL, the task evaluates the NULL equal to null values in the lookup.
If the lookup condition has multiple matches, the task returns any row.
Second source filters
You can apply filter conditions to filter the combined data.
You can configure the following types of filters:
Simple
To configure a filter condition, select a source field and configure the operator and value to use in the filter. You can select a field from either source. Fields from the original source are prefixed with the characters SRC_ or IN_SRC_.
When you define more than one filter condition, the task evaluates them in the order that you specify. The task evaluates the filter conditions using the AND logical operator to join the conditions. It returns rows that match all the filter conditions.
Advanced
Create a filter expression using the expression editor. You enter one expression that contains all filters. You can use source fields and built-in functions in the expression.