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:
1Lookup object details where you configure the connection, source type, lookup object, and multiple match behavior.
2Select 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.
- Treat multiple characters as a single delimiter. Treats the specified set of delimiters as one delimiter. For example, a source file contains the following record: abc~def|ghi~|~|jkl|~mno. If you specify the delimiter as ( ~ | ), Data Integration reads the record as three columns separated by two delimiters: abc~def|ghi, NULL, jkl|~mno. If you disable this option, Data Integration reads the record as nine columns separated by eight delimiters: abc, def, ghi, NULL, NULL, NULL, jkl, NULL, mno.
- Treat consecutive delimiters as one. Treats one or more consecutive column delimiters as one. The default is to treat consecutive delimiters as a null value.
- Row Delimiter. Line break character. Select a line break character from the list. The default is line-feed, \012 LF (\n).
- 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 advanced mode
When you use the Lookup transformation in advanced mode, 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 advanced mode 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:
•For a relational database connection, use an alias for each column in the SQL statement, for example:
SELECT COL1 AS COL1, COL2 AS COL2, COL3 AS COL3 from TABLE_NAME
•For other types of database connections, use SQL that is valid for the source database. You can use database-specific functions in the query.
To use a custom query as a lookup source, you must enable lookup caching.
When you change a custom query in a saved mapping, at design time Data Integration replaces the field metadata with metadata using the revised query. Typically, this is the desired behavior. However, if the mapping uses a relational source and you want to retain the original metadata, use the Retain existing field metadata option, which is available in the following connectors:
•JDBC V2 Connector
•PostgreSQL Connector
•MongoDB V2 Connector
•Couchbase Connector
When you use the Retain existing field metadata option, Data Integration doesn't refresh the field metadata during design time. Data Integration maps the existing fields with the fields from the revised query at run time. Fields that can't be mapped will cause run time failure.
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.
