To use a custom query as a source, select

Query

as the source type, and then click

Define Query

. When you define the query, use SQL that is valid for the source database. You can use database-specific functions in the query.

You can also use a custom query as a lookup source. For information about using a custom query in a Lookup transformation, see Custom queries.

When you create a custom query, enter an SQL SELECT statement to select the source columns you want to use.

Data Integration

uses the SQL statement to retrieve source column information. You can edit the datatype, precision, or scale of each column before you save the custom query.

For example, you might create a custom query based on a TRANSACTIONS table that includes transactions from 2016 with the following SQL statement:

SELECT TRANSACTION_ID, TRANSACTION_TOTAL, TRANSACTION_TIMESTAMP from dbo.TRANSACTIONS WHERE TRANSACTION_TIMESTAMP>’0:0:0:0 01/01/2016’

Data Integration

ensures that custom query column names are unique. If an SQL statement returns a duplicate column name,

Data Integration

adds a number to the duplicate column name as follows:

<column_name><number>

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. When you use this 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.