Fetch a Variable Number of Records with SQL

Hey, Ted:

If you want to use a variable for the number of rows to fetch in an SQL query, there is an alternative to the FETCH FIRST n ROWS technique you presented recently in Four Hundred Guru. Here is some code from an SQLRPGLE program shell I have used since V5R1. It uses a cursor instead of placing the number of rows directly into the SELECT statement.

c/Exec SQL                                         
c+ Fetch from C1 for :NbrRows rows into :RtnDtaSet 
c/End-Exec

--Rick

Rick's comment is in response to the last paragraph of the issue he refers to. His idea is to use a multi-row fetch, which requires that a structure of some sort (depending on the language) be defined to receive the data. In RPG, the data is placed into a multiple-occurrence data structure.

Here's another example that is a tad more complete. I have omitted the error-checking logic. The FETCH retrieves the number of rows specified by the first parameter.

Fqsysprt   o    f  132        printer               
                                                    
D CustData        ds                  occurs(12)    
D  CustNumber                    6  0               
D  CustName                     12                  
                                                    
D Rows            s              3p 0               
D Ndx             s              3p 0               
                                                    
C     *entry        plist                           
C                   parm                    Rows    
                                                    
C/exec sql                                          
C+           declare Customers cursor for           
C+             select cusnum, lstnam || ' ' || init 
C+               from qiws/qcustcdt                 
C/end-exec                                          
C/exec sql                                          
C+           open Customers                         
C/end-exec                                          
C/exec sql                                          
C+           fetch Customers for :Rows Rows         
C+             into :CustData                       
C/end-exec                                     
C                   for       ndx = 1 to Rows  
C     ndx           occur     CustData         
C                   except    PLine            
C                   endfor                     
C/exec sql                                     
C+           close Customers                   
C/end-exec                                     
C                   eval      *inlr = *on      
Oqsysprt   e            pline       1          
O                       Ndx           4        
O                       CustNumber       +   1 
O                       CustName         +   1 

After the FETCH retrieves the number of rows indicated by the ROWS variable, a simple loop lists the retrieved records.

--Ted

RELATED STORY

SQL Can Return One or a Few Records