RE: Best approach for Embedded SQL SELECT in V7R1

["Scott Mildenberger" <SMildenberger@xxxxxxxxxxxxxxxxxx>]

Below are the basics of the approach.  This is from a statistical
display comparing different trucks in our fleet.  The user can sort them
by different statistics.  I just have a function key that cycles through
the sort options.  I just have the SFL build in one procedure and the
Sorting done in another one.  Just call the build once based on the
user's criteria and then resort each time they hit the sort function
key.  After resorting the data I clear and reload the subfile.

Data Structure definition (pretty sure it could be externally described)
d AllData         ds                  dim(9999) qualified  
d   uaUnit                            like(uaUnit)         
d   s_NetRev                          like(s_NetRev)       
d   r_NetRev                          like(r_NetRev)       
d ....Rest of fields omitted.


Fetch
Exec SQL Fetch from C2 for 9999 rows into :AllData;

Sort
// Sort the array according to the sort order.               
select;                                                      
when SortOrder = 1;                                          
  sorta(d) %subarr(AllData(*).s_NetRev : 1 : AllDataRows);   
  SortedBy  = 'Revenue';                                     
  SortOrder =  2;                                            
when SortOrder = 2;                                          
  sorta(d) %subarr(AllData(*).s_TrpMiles : 1 : AllDataRows); 
  SortedBy  = 'Trip Miles';                                  
  SortOrder =  3;                                            
when SortOrder = 3;                                          
  sorta(d) %subarr(AllData(*).RPM : 1 : AllDataRows);        
  SortedBy  = 'Revenue per Mile';                            
  SortOrder =  4;                                            
when SortOrder = 4;                                          
  sorta(a) %subarr(AllData(*).DhPct : 1 : AllDataRows);      
  SortedBy  = 'Deadhead Percent';                            
  SortOrder =  0;                                            
other;                                                       
  sorta(a) %subarr(AllData(*).uaUnit : 1 : AllDataRows);     
  SortedBy  = 'Unit';                                        
  SortOrder =  1;                                            
endsl;                                                       

Scott


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Ryan
Sent: Tuesday, November 06, 2012 9:38 AM
To: RPG programming on the IBM i / System i
Subject: Re: Best approach for Embedded SQL SELECT in V7R1

Scott/Jon - Code samples for the DS array technique? Can I use an
externally described DS?


On Tue, Nov 6, 2012 at 11:32 AM, Jon Paris <jon.paris@xxxxxxxxxxxxxx>
wrote: