Re: RPGIV: Subfile "sort" thoughts

It would seem that to be useful and anywhere near accurate you'd need to
refresh/select the records with every change in the column to be sorted.  
That leads me to think you'd want to write an SQL selection for each column.
 Since you are thinking of page at a time anyway, this is no big deal.  

Probably you'd want to set your selection from the value in row 1 or the row
where the cursor is?  Otherwise you'd always start at the lowest value. 
 
The reason I say this is that a selection of people, zip code, age, and last
date contacted could easily provide hundreds of records.  If the subfile was
alphabetical by name and the user chose to view the subfile by zip code and
you simply resorted the single page you'd leave some awfully wide gaps
between zip codes.   
 
An advantage to writing 7 SQL statements is that each statement  can have
second and third fields in the ordering of the selected records.  You could
sort by (name within zip code), or (age and then zip code) or whatever made
sense.  
 
(I made up that example.)

---------------------------------------------------------
Booth Martin   http://www.MartinVT.com
Booth@xxxxxxxxxxxx
---------------------------------------------------------
 
-------Original Message-------
 
From: RPG programming on the AS400 / iSeries
Date: Wednesday, August 13, 2003 5:48:42 PM
To: rpgiv@xxxxxxxxxxxxxxx; 'RPG programming on the AS400 / iSeries'
Subject: RPGIV: Subfile "sort" thoughts
 
I have a situation were I need to help implement a subfile sort.
 
There is a Work-with panel that contains about 7 fields, all of which the
end-user wants to be able to sort by.
 
Nothing to unusual about this so far.
 
But in this situation the file is actually a dynamic file created with an
SQL UNION ALL statement that ends up producing about 9 million records.
 
I was thinking about a page=size subfile and will offer the end user a
filtering option to weed out unwanted transactions.
 
What I'm wondering is how to attack the subfile sort in this context; 9
million records+ doesn't lend itself well to any conventional techniques,
such as sorting a multiple occurrence data structure or even dynamic/runtime
querying via SQL or Open Query File as the performance will suck.
 
So I'm wondering about building an SQL view out of the UNION ALL statement,
and then using SQL further (pre-runtime) to create an index over each of the
7 fields. But I'm not sure if this is the right solution.
 
Comments? Suggestions?
 
 
 
Bob Cozzi
 
Cozzi Consulting
 
www.rpgiv.com