When you run the

mapping

task, the task generates a default SELECT statement that includes the lookup fields, output fields, and WHERE clause based on the lookup condition. If the Lookup transformation is an unconnected lookup, the SELECT statement includes the lookup fields and the return field, but the

mapping

task doesn't generate the WHERE clause from the lookup condition.

Override the default query on the

Advanced

tab of the Lookup transformation. Enter the entire SELECT statement in the

Lookup SQL Override

field. Use an alias for each column in the query. If you want to change the ORDER BY clause, append " --" to the end of the query to suppress the ORDER BY clause that the

mapping

task generates.

To reference incoming fields in the WHERE clause, configure parameter binding by adding a "?" before and after the field name. The query data changes based on the value of the data in the field. The following example includes a WHERE statement that references the Name field:

SELECT EMPLOYEE.NAME as NAME, max(EMPLOYEE.ID) as ID from EMPLOYEE WHERE EMPLOYEE.NAME=?NAME1?

If you add a function to the SQL statement, the return data type must match the data type of the alias column. For example, the data type of ID matches the return type of the MAX function:

SELECT EMPLOYEE.NAME as NAME, MAX(EMPLOYEE.ID) as ID FROM EMPLOYEE