Re: replace opnqryf with sql

David, I agree with your points.

RPG has, in my experience, unique capabilities that result in minimal code
for many solutions.

In your example, is there any reason OPNQRYF could not have been replaced
with SQL (maybe a QM Query),
and, therefore, eliminated the need to "simulate select distinct"
with an additional call ?




David FOXWELL <David.FOXWELL@xxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
01/20/2011 01:51 AM
Please respond to
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>


To
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
cc

Subject
replace opnqryf with sql






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.