Re: SQL - RRN() not in order

Terrence,

I agree (and am very conscious of) with the fact that the only way to guarantee a result ordered is with the ORDER BY clause.

That said, what I don't really understand is SQL using an index for reporting the RRN(). One would imagine that kind of things would be better doing a table scan.

Regards,

Luis Rodriguez

IBM Certified Systems Expert
eServer i5 iSeries Technical Solutions


--- On Sat, 7/26/08, midrange-l-request@xxxxxxxxxxxx <midrange-l-request@xxxxxxxxxxxx> wrote:

------------------------------

message: 2
date: Sat, 26 Jul 2008 00:13:35 -0400
from: Terrence Enger <tenger@xxxxxxxxxxxxxxxx>
subject: Re: SQL - RRN() not in order

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