Re: Can you do this in SQL

[Douglas Handy <dhandy1@xxxxxxxxxxxxx>]

David,

Nothing beats SQL for set at a time operations, IMHO.  But for record at a time
operations, it is the proverbial "it depends".

>a lot of people avoided
>OPNQRYF and continued using FMTDTA based on outdated
>information.

The big change here, other than possible early bugs in OPNQRYF, was when they
added the OPNQRYF option for ALWCPYDTA(*OPTIMIZE).  When specified -- it is not
the default -- you are telling the query optimizer it has permission to use a
sort routine (ala FMTDTA) when it would perform better than an index.  See the
help text for the keyword for more information.

>I recall these same types of arguments made for FMTDTA. Over the
>years OPNQRY got better and sort didn't. I am suspect a lot of the
>claims that OPNQRYF will perform better is based on old information.

With ALWCPYDTA(*OPTIMIZE), OPNQRYF can perform the same as FMTDTA when selecting
most of the records (but without the need to specify from/to positions <yuck>).
Or the query optimizer can decide to use an existing index or build a temporary
index.  It is like the best of both worlds.

SQL set operations are terrific.  Whether SQL cursors are more readable than
OPNQRYF is in the eye of the beholder, and is influenced greatly by the reader's
background.

No doubt as time goes on, young blood will be more versed in SQL than OPNQRYF
and thus over time it becomes "more readable" to an increasingly larger
percentage.

A shop with lots of "legacy" OPNQRYF -- like a shop with lots of legacy cycle
code -- will continue to have a need for some time for developers who understand
how to read and modify the code.

IMHO,
Doug