Re: SQL sort

Lots of good advice here. I expecially like Paul's suggestion to attend
COMMON. I strongly recommend that.
I also recommend a good SQL book. The one I use is a little dated by SQL
standards but is a good start "SQL/400 Developer's Guide" by Paul Conte
and Mike Cravitz.
We have a developer who does some awesome things with SQL and he just
originally learned it all by the IBM manuals.

General rule of thumb is to query the physical and let the system
determine which logicals (indexes) to use. If you start using CREATE VIEW
it's perfectly acceptable to query those. I think the order of the keys
on these indices is Join, then where, then order by.

iNav has a way to see what indices are being recommended. You may want to
query QSYS2.SYSIXADV. Don't just build them all. Use some common sense.
Dieter gave some reasons why. IBM i is not like the bad old days of
OS/400 in which every index you created could be a significant impact on
performance. I still remember John Sears giving lectures on how too many
LF's over a PF caused this huge performance hit because when you wrote to
the PF it then had to update all those LF's. But, there is still some
hit. Not like it used to be, but still some hit. Again, just use some
common sense in creating the recommended indices.

There are some who say if you are using "cursor"s in SQL you are doing it
wrong. I disagree with them. In your subfile application you will have
to use a cursor (if you want to use SQL to access the data). Once you
learn how to use a cursor you should be able to handle subfile
programming. You also may want to study "multiple-row-fetch", "for x
rows" and so on.

You could also google for: sql subfile programming
Lots of good hits, especially the first 10.



Rob Berendt