Transformations > Lookup transformation > Lookup object
  

Lookup object

The Lookup object is the source object that Data Integration queries when it performs the lookup. The lookup object is also called the lookup source.
Select the lookup source on the Lookup Object tab of the Properties panel. The properties that you configure for the lookup source vary based on the connection type
The following image shows the Lookup Object tab for a relational lookup:
The Lookup Object tab for a relational lookup contains the following lookup object details: Connection, Source Type, Lookup Object, and Multiple Matches. It also contains advanced properties. The fields on the Lookup Object tab vary based on the connection type.
  1. 1. Lookup object details where you configure the connection, source type, lookup object, and multiple match behavior.
  2. 2. Select the lookup source from the mapping inventory.
You can select the lookup source in the following ways:
Select the connection and lookup object.
In the Lookup Object Details area, select the connection, source type, and lookup object. You can also create a new connection.
Select the lookup source from the mapping inventory.
If your organization administrator has configured Enterprise Data Catalog integration properties, and you have added objects to the mapping from the Data Catalog page, you can select the lookup source from the Inventory panel. If your organization administrator has not configured Enterprise Data Catalog integration properties or you have not performed data catalog discovery, the Inventory panel is empty. For more information about data catalog discovery, see Mappings.
Use a parameter.
You can use an input parameter to define the connection or lookup object when you run the mapping task. For more information about parameters, see Mappings.
Use a custom query
You can use a custom query to reduce the number of columns to query. You might want to use a custom query when the source object is large.
You must also specify the transformation behavior when the lookup returns multiple matches.

Lookup object properties

When you configure a lookup, you select the lookup connection and lookup object. You also define the behavior when a lookup condition returns more than one match.
The following table describes the lookup object properties:
Property
Description
Connection
Name of the lookup connection.
Source Type
Source type. For database lookups, the source type can be single object, parameter, or query. For flat file lookups, the source type can be single object, file list, command, or parameter.
Lookup Object
If the source type is a single object, this property specifies the lookup file, table, or object.
If the source property is a file list, this property specifies the text file that contains the file list.
If the source type is a command, this property specifies the sample file from which Data Integration imports the return fields.
Parameter
If the source type is a parameter, this property specifies the parameter.
Define Query
If the source type is a query, displays the Edit Custom Query dialog box. Enter a valid custom query and click OK.
Multiple Matches
Behavior when the lookup condition returns multiple matches. You can return all rows, any row, the first row, the last row, or an error.
If you choose all rows and there are multiple matches, the Lookup transformation is an active transformation. If you choose any row, the first row, or the last row and there are multiple matches, the Lookup transformation is a passive transformation.
Formatting Options
File formatting options which are applicable if the lookup object is a flat file.
Opens the Formatting Options dialog box to define the format of the file. Configure the following file format options:
  • - Delimiter. Delimiter character.
  • - Text Qualifier. Character to qualify text.
  • - Escape character. Escape character.
  • - Field labels. Determines if the task generates field labels or imports labels from the source file.
  • - First data row. The first row of data. The task starts the read at the row number that you enter.
Command
If the source type is a command, this property specifies the command that generates the file list.
For more information about file lists and commands, see File lists. For more information about parameters and file formats, see Mappings.

Multiple match policy restrictions

When you configure a lookup, you define the behavior when a lookup condition returns more than one match. Some types of lookups have restrictions on the multiple match policy.
The following types of lookups have multiple match policy restrictions:
Uncached lookups
Some connector types do not support the multiple match policies Return first row and Return last row in uncached lookups. If you select either of these policies, and the connector does not support the policy in uncached lookups, Data Integration enables the Lookup Caching Enabled advanced property, and you cannot edit it.
Lookups that use a dynamic cache
If the Lookup transformation uses a dynamic cache, you must configure the multiple match policy to return an error. Other multiple match policies are not supported.
Salesforce lookups
When you perform a lookup against a Salesforce object, you can return any row or return an error.
Lookups in elastic mappings
When you use the Lookup transformation in an elastic mapping, you can return all rows, return any row, or return an error. The multiple match policies Return first row and Return last row are not supported.
When you define the behavior for multiple matches in an elastic mapping to return an error, the Lookup transformation drops duplicate rows and does not include the duplicate rows in the log files.
For more information about the multiple match policies supported by different connectors, see the help for the appropriate connector.

Custom queries

You can create a custom query for database lookups. You might create a custom query to reduce the number of columns to query.
To use a custom query as a lookup source, select Query as the source type, and then define the query. When you define the query, enter an SQL SELECT statement to select the source columns that you want to use. Data Integration uses the SQL statement to retrieve source column information.
When you use a custom query in a lookup transformation, use the following format for the SQL statement:
To use a custom query as a lookup source, you must enable lookup caching.
Tip: Test the SQL statement you want to use on the source database before you create a custom query. Data Integration does not display specific error messages for invalid SQL statements.