I've run into a snag with an index on DB2 within a large transaction table. The DBA has utilized the FIELDNAME (a date field) as an index.
If I simply use a
WHERE FIELDNAME GT '2004-01-19' AND
FIELDNAME LT '2004-12-31'

DB2 appears to ignore the index and scan the table. If within direct SQL I utilize a BETWEEN command on those dates DB2 will utilize the index (and speed things up).

Any suggestions of a FOCUS equalivent of a between short of direct SQL? (DB2/MVS) Focus 7.2

The syntax equivalent of SQL BETWEEN is (informally):

TABLE FILE ...<br />...<br />WHERE <field> FROM <value1> TO <value2><br />...<br />END

but I did not checked yet, how is it translated to SQL for the relational adapter.

Regards
Grzegorz

Many thanks!
That appears to be taking advantage of the index.

How about using SQL Passthru?

SET SQLENG=<engine>
SQL
<native_sql>
END

It helps writing eficient code (specialy when timestamp fields used :-)

Pranas