DSXchange

Hi,
We need to pass 2 column values from a Seq Stage to a User defined Query in ODBC lookup Stage ( the Number of rows concerned is less ). We are doing a MAX on a column's value in the query.
How can we do it ?

Thanks

dsrules

Define those two columns (and no others) as Key on the reference input link. In the Transformer stage drag the two columns from the sequential file into the reference key expression fields.

Those two columns will automatically be represented in the WHERE clause on the generated SQL in the ODBC stage. Examine that, and include the same syntax in any user-defined SQL.

"Key" in this context means "search key" rather than "primary key".

Indeed you can use generated, rather than user-defined, SQL in the ODBC stage. Add the MAX() function in the Derivation cell in the Columns tab. View the generated SQL - it's going to be very close to what you want. You may also require a GROUP BY clause on the Selection tab.

Prefer generated to user-defined SQL wherever possible; it gives you insurance against change of database in future.

Thanks a lot Ray ! Will try it.

dsrules