Using RRN in SQL

Here is an example of using SQL to select a record based on key fields AND the highest RRN.

Exec SQL                                                              
  Select a.dbID, a.dbCde, a.dlstnm, a.dfstnm, a.depdob                
   into :wk_dbID, :wk_dbCde, :wk_dLstNm, :wk_dFstNm, :wk_Dob          
   from File001 a join                                                
    (select dbID, dbCde, max(effdte) meffdte, max(rrn(File001)) mrrn  
     from File001 group by dbID, dbCde) b                             
   on a.dbID=b.dbID and a.dbCde=b.dbCde and a.effdte=b.meffdte        
      and rrn(a) = b.mrrn                                             
   where a.dbID=:pi_dbID and a.dbCde=:pi_dbCde ;           

However, there is a caveat here. The intent is to retrieve the most current record for the compound key. But, if the file description has specifed re-use deleted records, this technique will not work. The highest RRN may not be the most recent record written.

If a cursor has been declared referencing the relative record (RRN) then the RRN retrieve can be used to update the record, without referencing a key, or trying to code around a non-unique key.


       EXEC SQL                                                                                                         
          declare AEP811_Csr insensitive cursor                                                                         
             for (select xData, xSts, RRN(AEP811) from AEP811 where xSts = 0);
       . . .

          EXEC SQL                                                                                                      
             FETCH NEXT FROM AEP811_Csr                                                                                 
             INTO :dataRec;          

	. . .


          //*------------------------------------------------------------*/                                             
          //* Set status flag to 9, indicating the record was processed. */                                             
          //*------------------------------------------------------------*/                                             
                                                                                                                        
          EXEC SQL                                                                                                      
            Update AEP811 set xSts = 9                                                                                  
              Where RRN(AEP811) = :htmRRN;