Re: SQL - RRN() not in order

[Terrence Enger <tenger@xxxxxxxxxxxxxxxx>]

Let me remind people--I confess, myself included--that if you want
results in a specific order, you must specify that order explicitly.
Even selecting from a keyed logical does not guarantee any particular
sequence.

This is hard to remember because it so often happens that the system
will choose a strategy which returns rows in the "right" order by
accident.  Sigh!

Cheers,
Terry.


On Fri, 2008-07-25 at 07:10 -0700, Luis Rodriguez wrote:
> Hi List,
>
> Running some tests  (V5R3) I found this strange (at least for me) results with SQL RRN:
> (FILE is a (DDS) file with no index and zero deleted records)
>
> Select RRN(file) from FILE;
> ...........................
> RRN ( file )
>             20
>            471
>             73
>            382
>            593
>             .
>             .
>             .
>             88
>             89
>             90
>             91
>            609
>
> Select RRN(file),  file.* from FILE;
> ..................................
>    RRN ( file )       (Fields...)
>                 1         xxxxx
>                 2         xxxxx
>                 3         xxxxx
>                 4         xxxxx
>                 5         xxxxx
>                 6         xxxxx
> etc.
>
> Even this sorts the output by RRN():
> Select RRN(file) from FILE WHERE RRN(file) < 700;
>
> Running the sentence under debug (STRDBG) shows that SQL decided to use an index (LF). 
>
> My question is: Why does RRN() needs an index?. Moreover, the selected index is a join file(3 Files!!)
>
> This is not giving any problem for me right now, I'm just curious why this is so.
>
> Any ideas?
>
>
>
> Regards,
>
> Luis Rodriguez
>
>
> 	
>
> IBM Certified Systems Expert
> eServer i5 iSeries Technical Solutions
>
>
>