Description
The Database lookup step allows you to look up values in a database table,this will go to database to lookup for each and every record. Lookup values are added as new fields onto the stream.
Options
The following table describes the available options for configuring the database lookup:
Option Description
Step name Name of the step; this name has to be unique in a single transformation.
Connection Database connection to the lookup table.
Lookup schema Database schema containing the lookup table.
Lookup Table Name of the database table used for the lookup.
Enable cache? Enables caching of database lookups. This means that once a key (or group of key) has been looked up, the looked up values are stored, and returned again the next time this key (or group of key) is being looked up (without incurring the cost of a database call).
Cache size in rows The size of the cache (number of rows).
Load all data from table
Pre-loads the cache with all the data present in the lookup table. This may improve performance by avoiding database calls.
However, if you have a large table, you risk running out of memory.
IMPORTANT: In memory look-ups can lead to different results because of the differences in the way your database compares data. For example, if your database table allows case-insensitive comparisons, you may get different results with this option. The same goes for data with trailing spaces. Those are ignored in certain databases when using certain character data types. *
Keys to look up table The keys and conditions to perform the database lookup.
Values to return table The fields from the lookup table to add to the output stream.
"New name" allows you to use a different name if the database column name is inappropriate.
"Default" is the value returned (instead of null) if the lookup fails. Note that enabling error handling on the Database Lookup step will redirect all failed lookup rows to the error handling step, instead of adding them to the main output steam with null/default values.
"Type" is the type of the output field.
Do not pass the row if the lookup fails Enable to avoid passing a row when lookup fails.
Fail on multiple results? Enable to force the step to fail if the lookup returns multiple results.
Order by If the lookup query returns multiple results, the ORDER BY clause helps you to select the record to take. For example, ORDER BY would allow you to pick the customer with the highest sales volume in a specified state.
Get Fields Click to return a list of available fields from the input stream(s) of the step.
Get lookup fields Click to return a list of available fields from the lookup table that can be added to the step's output stream.
