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
Dynamic User Defined Query
Moderators: chulett, rschirm, roy
Dynamic User Defined Query
______________________________________
"Everytime I close the door on reality, it comes in through the windows." - Jennifer Yane
"Everytime I close the door on reality, it comes in through the windows." - Jennifer Yane
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.