Re: Logical file in RPG

The INDEX can be created without the WHERE clause, and an SQL SELECT can use the keyed access path for the selection on its WHERE clause, just as the program uses the access path with an F-spec. As an SQLRPG without the F-spec, the program can issue a SET Date_Field=(CURRENT DATE - 1 YEARS) so the WHERE clause of the SELECT can use a host variable [or parameter marker] for which selection will be much like a SETLL and READ; e.g. "SELECT ... FROM ATABLE WHERE DATE_FIELD>=:Date_Field".

The reply expressing a concern that there be an "index on the file that is maintained immediately" is met with the CREATE INDEX, even without a WHERE. The reply expressing a concern about how perhaps "if I don't want to use the date as a key" and that maybe "I may want to set up logicals using different key lists" are quite well suited to SQL. In fact such varied requirements are often an impetus to move away from using the RLA to using the SQL. It seems from those replies, that there is a deep desire to just avoid the SQL and to continue using an F-spec; that the attempt to create and use the SQL selective INDEX was merely a means to effect that avoidance, and failing its creation, some other solution that does not involve the RPG becoming SQLRPG is still the direction.

If avoiding the SQL is so imperative, then consider:

≥ Use a VIEW instead of an INDEX; use the same WHERE clause. The special register CURRENT DATE is acceptable in a VIEW. With the existing LF keyed as desired, the selection in the VIEW can use that; a SELECT in a VIEW is effectively the same as the one embedded, except the OPEN of the VIEW will not have an order. But that was not stated as a requirement, only the selection; even if order was subtly implied for the implication of using an F-spec.

≥ There is OPNQRYF to use a shared ODP; the KEYFLD(*FILE) can be used. With the existing keyed logical keyed as desired, the QRYSLT() specification will effectively position much like the SETLL would; but none of the non-selected data is available to the program. This interface basically mimics the LF with the selection, but allows other dynamic selection. Note: The query request could be composed as a join instead of selection [e.g. to avoid QRYSLT specifications], and the joined-to file could be a one-row table with the desired date value as comparison to the key field in ATABLE. Issue an update to the data in the one-record file to set the date field to the desired value. Open the query after the update.

≥ Create the LF with the DATE_FIELD as key. Then generate a source member with the same key and a COMP keyword, dynamically update the test comparison value in the COMP keyword in the source to the value represented by the expression (CURRENT DATE - 1 YEARS), and create that new INDEX. Specify the DYNSLT keyword, and the keyed access path for the LF will build probably subsecond [just sharing the existing AccPth in the already-created LF. Without the DYNSLT, the database may use a feature called build-index-from-index to generate the access path faster than reading the row data [the query features can];;; but if not, the LF creation can be scheduled daily versus created at run-time. And depending on how the application(s) function, the maintenance of the dynamically created access path could be set to delayed so that the changes since the last open are all that is required to be performed, versus a full index build [be it index-from-index or from the record data].

FWiW: If the join logical file allowed tests other than equal between its JFLD specifications, the use of a JLF and updating the one-row file with the desired date value would be nice.