Connected and Unconnected Lookups
You can configure a connected Lookup transformation or an unconnected Lookup transformation. A connected Lookup transformation is a transformation that has input and output ports that you connect to other transformations in a mapping. An unconnected Lookup transformation appears in the mapping, but is not connected to other transformations.
An unconnected Lookup transformation receives input from the result of a :LKP expression in a transformation such as an Expression transformation or Aggregator transformation. The :LKP expression passes arguments to the Lookup transformation and receives a result back from the Lookup transformation. The :LKP expression can pass lookup results to another expression in the Expression or Aggregator transformation to filter results.
The following table lists the differences between connected and unconnected lookups:
Connected Lookup | Unconnected Lookup |
|---|
Receives input values directly from the pipeline. | Receives input values from the result of a :LKP expression in another transformation. |
Use a dynamic or static cache. | Use a static cache. |
Cache includes the lookup source columns in the lookup condition and the lookup source columns that are output ports. | Cache includes all lookup and output ports in the lookup condition and the lookup/return port. |
Returns multiple columns from the same row or insert into the dynamic lookup cache. | Returns one column from each row to a return port. |
If there is no match for the lookup condition, the Integration Service returns the default value for all output ports. If you configure dynamic caching, the Integration Service inserts rows into the cache or leaves it unchanged. | If there is no match for the lookup condition, the Integration Service returns NULL. |
If there is a match for the lookup condition, the Integration Service returns the result of the lookup condition for all lookup/output ports. If you configure dynamic caching, the Integration Service either updates the row the in the cache or leaves the row unchanged. | If a match occurs for the lookup condition, the Integration Service returns the result of the lookup condition to the return port. |
Passes multiple output values to another transformation. Link lookup/output ports to another transformation. | Returns one output value to another transformation. The Lookup transformation return port passes the value to the port that contains the :LKP expression in the other transformation. |
Supports user-defined default values. | Does not support user-defined default values. |
Connected Lookups
A connected Lookup transformation is a Lookup transformation that is connected to a source or target in a mapping.
The following figure shows a mapping with a connected Lookup transformation:
When you run a mapping that contains a connected Lookup transformation, the Integration Service performs the following steps:
- 1. The Integration Service passes values from another transformation to input ports in the Lookup transformation.
- 2. For each input row, the Integration Service queries the lookup source or cache based on the lookup ports and the lookup condition in the transformation.
- 3. If the transformation is uncached or uses a static cache, the Integration Service returns values from the lookup query.
If the transformation uses a dynamic cache, the Integration Service inserts the row into the cache when it does not find the row in the cache. When the Integration Service finds the row in the cache, it updates the row in the cache or leaves it unchanged. It flags the row as insert, update, or no change.
- 4. The Integration Service returns data from the query and passes it to the next transformation in the mapping.
If the transformation uses a dynamic cache, you can pass rows to a Filter or Router transformation to filter new rows to the target.
Unconnected Lookups
An unconnected Lookup transformation is a Lookup transformation that is not connected to a source or target in the mapping. Call the lookup with a :LKP expression in a transformation that allows expressions.
The syntax for the lookup expression is :LKP lookup_transformation_name(argument, argument, ...)
The order in which you list each argument must match the order of the lookup conditions in the Lookup transformation. The Lookup transformation returns the result of the query through the Lookup transformation return port. The transformation that calls the lookup receives the lookup result value in the port that contains the :LKP expression. If the lookup query fails to return a value, the port receives a null value.
When you perform an unconnected lookup, you can perform the same lookup multiple times in a mapping. You can test the results of the lookup in another expression and filter rows based on the results.
When you run a mapping that contains an unconnected Lookup transformation, the Integration Service performs the following steps:
- 1. An unconnected Lookup transformation receives input values from the result of a :LKP expression in another transformation, such as an Aggregator transformation, Expression transformation, or Update Strategy transformation.
- 2. The Integration Service queries the lookup source or cache based on the lookup ports and condition in the Lookup transformation.
- 3. The Integration Service returns a value through the return port of the Lookup transformation.
- 4. The Integration Service passes the return value to the port that contains the :LKP expression.
