You can override the lookup SQL query for relational lookups.

If you override the lookup query, you must also enable lookup caching for the transformation.

Enter the entire SELECT statement using the syntax that is required by the database.

Enclose all database reserved words in quotes.

If the ORDER BY clause contains multiple columns, enter the columns in the same order as the fields in the lookup condition.

If the

mapping

task uses pushdown optimization, you cannot override the ORDER BY clause or suppress the generated ORDER BY clause with comment notation.

If multiple Lookup transformations share a lookup cache, use the same lookup SQL override for each Lookup transformation.

When you configure a Lookup transformation that returns all rows, the

mapping

task builds the lookup cache with sorted keys. When the transformation retrieves all rows in a lookup, the

mapping

task builds the data cache with the keys in sorted order. The

mapping

task cannot retrieve all the rows from the cache if the rows are not sorted. If the data is not sorted on the keys, you might get unexpected results.

You cannot include parameters in the lookup SQL override.

If you configure a lookup SQL override and a lookup source filter in the same transformation, the

mapping

task ignores the filter.