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 and specify a FROM clause.

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.