RE: SQL FETCH FIRST and columns in ORDER BY

["Dennis Lovelady" <iseries@xxxxxxxxxxxx>]

> You're correct to want to eliminate the cursor in this case.  SELECT
> HSACTION INTO :myHsAction FROM ... WHERE ... ORDER BY ... FETCH FIRST
> ROW ONLY will perform slightly better and is simpler to code and 
> maintain.  Another possible approach is to use a subselect that gets a MAX

<snip> Great!  Thanks!  I had avoided SELECT INTO simply because of lack of
trust for FETCH FIRST.  So the FETCH FIRST... reduces the set AFTER the
ORDER BY, right?

> Finally, you use an RRN in the ORDER BY.  Is there absolutely no other
> way to get the right order?  Like another table column to serve as a
> secondary order-by column.  RRN is not keyed internally so I recommend
> avoiding it when possible.

In this case, alas, no.  I would prefer to have a SEQUENCE by which these
rows could be ordered (allowing us to REUSEDLT, for example), but the file
has hundreds of millions of rows, and the client understandably worries
about such changes.  I've not seen Production, but as I understand it, the
file is so large that RGZPFM is out of the question.  Not sure how they
purge.

> Hth, Elvis

It does, thanks.

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"The brain is a wonderful organ; it starts the moment you get up in the
morning and does not stop until you get to the office."
        -- Robert Frost 
Life is not a journey to the grave with the intention of arriving safely in
one pretty and well preserved package, but to skid across the line
broadside, thoroughly used up, worn out, leaking oil, shouting: "GERONIMO!"