Fetch a Variable Number of Records with SQL
June 15, 2005 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
|
