SQL & RRN Access

SQL can access rows by relative record number (RRN).

Finding MAX RRN

Here is an example of using SQL to select a record based on key fields AND the highest RRN. The file does have key fields defined. However, the keys are not unique. Multiple transactions may exist with the same keys. Finding the most recent transaction becomes a challenge. SQL may use the MAX function in conjunction with RRN to find the most recent transaction. It showuld be the row with the highest relative record number. 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. 

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 ;

Update by RRN

In a database (such as mentioned in the first example) where key fields might not be unique, the RRN function may be useful when declaring a cursor. A cursor may be created referencing the relative record (RRN). Then the RRN retrieved can be used to update the record, without referencing a key, or trying to code around a non-unique key. The code snippett illustrates a table update, changing the status of a row, using RRN. 

       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;