An unconnected Lookup transformation is a Lookup transformation that is not connected to other transformations in a mapping. A transformation in the mapping pipeline calls the Lookup transformation with a :LKP expression. The unconnected Lookup transformation returns one column to the calling transformation.
You can use an unconnected Lookup transformation to perform a lookup against the following types of data objects:
•Flat file
•Relational database
•Amazon Redshift V2
•Amazon S3 V2
•Google BigQuery V2
•Microsoft Azure Synapse SQL
•Snowflake Data Cloud
The following table lists the differences between connected and unconnected Lookup transformations:
Functionality
Connected lookup
Unconnected lookup
Input values
Receives input values directly from the mapping pipeline.
Receives input values from the result of a :LKP expression in another transformation.
Cache
Cache includes all lookup columns used in the mapping. This includes columns in the lookup condition and columns linked as output fields to other transformations.
Can use static or dynamic cache.
Cache includes all lookup/output fields in the lookup condition and the lookup/return field.
Cannot use dynamic cache.
Return values
Returns multiple values from the same row.
Returns the specified field for each row.
Lookup conditions
If there is no match for a lookup condition, Data Integration returns the default value for all output fields.
If there is a match, Data Integration returns the results of the lookup condition for all lookup/output fields.
If there is no match for the lookup condition, Data Integration returns NULL.
If there is a match, Data Integration returns the result of the lookup condition to the return field.
Output values
Passes multiple output values to another transformation. Links lookup/output fields to another transformation.
Passes one output value to another transformation. The lookup/output/return field passes the value to the transformation that contains the :LKP expression.
Configuring an unconnected Lookup transformation
To configure an unconnected Lookup transformation, select the Unconnected Lookup option, add incoming fields, configure the lookup condition, and designate a return value. Then configure a lookup expression in a different transformation.
1On the General tab of the Lookup transformation, enable the Unconnected Lookup option.
2Create the incoming fields.
On the Incoming Fields tab of the Lookup transformation, create an incoming field for each argument in the :LKP expression. For each lookup condition you plan to create, you need to add an incoming field to the Lookup transformation. You can create a different field for each condition, or use the same incoming field in more than one condition.
3Designate a return value.
You can pass multiple input values into a Lookup transformation and return one column of data. Data Integration can return one value from the lookup query. Use the return field to specify the return value.
4Configure a lookup expression in another transformation.
Supply input values for an unconnected Lookup transformation from a :LKP expression in a transformation that uses expressions such as an Expression, Aggregator, Filter, or Router transformation. The arguments are local input fields that match the Lookup transformation input fields used in the lookup condition.
Calling an unconnected lookup from another transformation
Supply input values for an unconnected Lookup transformation from a :LKP expression in another transformation such as an Expression transformation or Aggregator transformation. You can call the same lookup multiple times in one mapping. You cannot call an unconnected lookup from a Joiner or Java transformation.