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.
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.