On the Blaze or native engines, NULL results are treated as FALSE.
Return Any Row
The transformation returns any of the rows that match the lookup condition. The transformation creates an index based on the key ports instead of all Lookup transformation ports. When you choose this option, performance can improve because the process of indexing rows is simpler.
Return All Rows
The Lookup transformation returns all rows that match the lookup condition.
When the Lookup transformation uses a static cache or no cache, the Data Integration Service marks the row as an error. The Lookup transformation writes the row to the session log by default, and increases the error count by one.
When the Lookup transformation has a dynamic cache, the Data Integration Service fails the session when it encounters multiple matches. The session fails while the Data Integration Service is caching the lookup table or looking up the duplicate key values.
Also, if you configure the Lookup transformation to output old values on updates, the Lookup transformation returns an error when it encounters multiple matches. The transformation creates an index based on the key ports instead of all Lookup transformation ports.
Rules and Guidelines
Mapping validation fails in the following situations:
Case sensitivity is disabled.
The lookup condition contains binary data type.
The lookup condition uses a field with a complex data type.
The cache is configured to be shared, named, persistent, dynamic, or uncached. The cache must be a static cache.
The mapping fails in the following situation:
The transformation is unconnected and used with a Joiner or Java transformation.
If an HBase lookup does not result in a match, it generates a row with null values for all columns. You can add a Filter transformation after the Lookup transformation to filter out null rows.