Re: replace opnqryf with sql

On 1/20/11 12:51 AM, David FOXWELL wrote:

This is often the advice, but it seems easier to say than do.

I was just given a small program to write where, in the good 'ol
days, I'd have done an OPNQRYF followed by a client file read with
the RPG cycle and use the calculation specs beginning or end to
treat the client once only if it occurs multiple times in the file.

As I can no longer write OPNQRYF that complex without looking it up,
I plunged straight into the RPGSQLE solution :

Declare result set DS
Dynamically prepare a cursor
Declare cursor
Open cursor
Then logic to Fetch
Manage SQL errors after each operation.

The query was a select distinct on one file only using a date field
that was returned from a subprocedure.

The CLP that calls the RPG is now very clear, but the RPG is not as
simple as it would have been had I used the cycle. Also during
development, I had to add another field to the result set. As my
Fetch was in a subroutine and it was returning its results as
parameters, this meant changing the DS, the parameters, the
prototype, the CALLP and the cursor. With RLA, the extra field would
already have been available.

The non SQL solution would have been :
CALLP getDateParameter
OPNQRYF using dateParameter
CALL RPG using cycle to 'simulate' select distinct.

If record collation does not matter then either a permanent logical file or a LF specific to the request or job [thus likely using QTEMP], created by the SQL CREATE VIEW before the file is opened by the RPG program, can be an effective means to replace an OPNQRYF request. If some specific row ordering is required, then OPNQRYF QRYFILE((TheView)) KEYFLD() SRTSEQ() can be utilized but with almost no "complexity", as the Open Query File request in such a scenario is not required to do selection, grouping, etc., just the ORDER BY.

Regards, Chuck