Thanks Chuck this is very helpful...
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: 11 April 2013 15:28
To: rpg400-l@xxxxxxxxxxxx
Subject: 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.
--
Regards, Chuck
On 11 Apr 2013 04:13, Klugman, Luke wrote:
Can anyone recommend the best way to create/use a logical file that I
can use in the F specs of an RPG program that will allow me to key a
file with a selection on a date field.
Basically I have a field with a date filed but I only want to include
dates within the current year to date. I thought the best way to do
this would be to create a SQL index as follows:
CREATE INDEX ATABLEI1 ON ATABLE (DATE_FIELD) where Date_Field>=
(CURRENT DATE - 1 YEARS)
but you cannot use the current date function with the create index
command.
I also thought of creating a view with a sub-select which contains an
order by statement, but this doesn't give me any key fields.
Any help would be greatly appreciated.
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
-----------------------------------------------------------------------
The information contained in this email is confidential and may also contain privileged information. Sender does not waive confidentiality or legal privilege. If you are not the intended recipient please notify the sender immediately; you should not retain this message or disclose its content to anyone.
Internet communications are not secure or error free and the sender does not accept any liability for the content of the email. Although emails are routinely screened for viruses, the sender does not accept responsibility for any damage caused. Replies to this email may be monitored.
For more information about the Tullett Prebon group of companies please visit the following web site: www.tullettprebon.com
-----------------------------------------------------------------------
midrange.com
