Re: Must I use OPNQRYF

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/20/2012 10:24 AM, Jeff Crosby wrote:

That being said, do you know what I miss the most when I use an
embedded SQL cursor instead of OPNQRYF for reports? Don't laugh,
but it's the control (level) breaks built into RPG for report
programs. They make it so clean and simple for report programs.
And no, I am not at all interested in regurgitating the 'cycle'
debate.

I do the majority of my programming now in Java against a PostgreSQL
database. I, too, miss the RPG cycle and found that I can emulate it
somewhate using window functions, like so:

select *,
lag(form7group) over(partition by form7group_id order by
form7group_id, total_consumers_affected desc) is distinct from
form7group as form7group_changed
from outage_bymonth_bycategory where oyear=? and omonth=?

In this example, the result is a new column "form7group_changed" is
added to the result set that is true anytime a 'level break' occurs
and false the remainder of the time. Makes it easy to change display
on jsp output.

I only have a V5R3 i5 to play with so I don't have window functions to
try it but I'd guess the same can be done on DB2.

It's not real level breaks but some of the functionality can be driven
down to the database level with some creativity and it saves that
annoying crap in the code: if lastvalue <> currentvalue...

Regards,

Barry
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFPQnrrCFu3bIiwtTARAl+rAJ4wXyJb8C1obu73+nIP7nG9KbMH8wCfSgYF
MXfTzSlnADP+m/ra1lMt4pc=
=qQcT
-----END PGP SIGNATURE-----