Re: Subfile "sort" thoughts

The problem that I've run into with sorting on columns in joined files is
keeping the cursor updatable.  If the optimizer has to create a temporary
file then the cursor is not updatable and the user will never see database
changes until the cursor is closed and reopened.




                                                                                
                                                      
                      "Bob cozzi"                                               
                                                      
                      <cozzi@xxxxxxxxx>         To:       
<rpgiv@xxxxxxxxxxxxxxx>, "'RPG programming on the AS400 / iSeries'"         
                      Sent by:                   <rpg400-l@xxxxxxxxxxxx>        
                                                      
                      rpg400-l-bounces@x        cc:                             
                                                      
                      idrange.com               Subject:  Subfile "sort" 
thoughts                                                     
                                                                                
                                                      
                                                                                
                                                      
                      08/13/2003 10:52                                          
                                                      
                      AM                                                        
                                                      
                      Please respond to                                         
                                                      
                      RPG programming on                                        
                                                      
                      the AS400 /                                               
                                                      
                      iSeries                                                   
                                                      
                                                                                
                                                      
                                                                                
                                                      




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



_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.