General ODBC Use Question

Yes ODBC could be used.

How depends on whether you want to do the SQL yourself or invoke a Stored Procedure and whether you want to do this via a TPS proc or within an Xlate.

You will need to code some Tcl in any case.

Here we would require a Stored Procedure and where we do it would depend on the circumstances.

However, the very best solution is to have the date provided by the source system which is where it should come from.

What Jim said is an IT architecture best practise – have the data source provide the stored procedure. It establishes an interface (contract) that frees you from knowing the underlying data schema or having to deal with changes to it; and it is simpler to call.

The only caveat is if high availability is important, and if so, and if the data source is on another server, you could have a nightly copy of the source table to a local table and query that instead.