SQL FETCH Operations
Retrieving information in SQL
Scroll cursors allow directional options such as FETCH NEXT, FETCH FIRST, FETCH LAST, FETCH PRIOR, FETCH RELATIVE and FETCH ABSOLUTE. In addition, changes to the underlying tables are reflected in the cursor. There is a performance consideration; the cursor is re-evaluated on each call. In this case the FETCH NEXT returns the row into an externally defined data structure.
Fetch Next
Dcl-DS Control extName('CSGCTLPF') preFix(n_) ;
END-DS;
.
.
.
*=================================
* reading next record
*=================================
Dcl-Proc getNextCSGtableEntry export ;
Dcl-PI getNextCSGtableEntry ind End-PI ;
Dcl-S rowFound ind ;
EXEC SQL FETCH NEXT
FROM CSGcontrolCursor
INTO :Control;
IF SQLSTT <> SQLsttOK ;
rowFOUND = *OFF ;
ELSE ;
rowFOUND = *ON ;
ENDIF ;
RETURN rowFOUND ;
End-Proc getNextCSGtableEntry
Multiple Row Fetch
The code below works with an insensitive cursor. Using the diagnostic function, the file cursor is checked immediately after open to determine how many rows match the selection criteria. The FETCH FOR N ROWS retrieves a block of data INTO a multiple occrrence data structure. Since the data necessary for the process has been returned to the program, the cursor is then closed. Processing the data is then performed through the DS using %OCCUR.
**FREE
ctl-Opt DEBUG(*YES) OPTION(*SRCSTMT : *NODEBUGIO)
DFTACTGRP(*NO) Main(CSG078RP) EXTBININT(*YES)
ActGrp('DLYRPT') BndDir( 'QC2LE' );
//********************************************************************
//* Program Name : CSG078RP *
//* Program Description : CSG IMB missing scan summary *
//* Date : 1/22/19 *
//* Author : Steve Croy *
//********************************************************************
//* *
//* Modification Log *
//* *
//* Project Marker Date Description *
//* *
//********************************************************************
Dcl-F CSG078PR printer(*ext) oflind(*in73) usropn ;
Dcl-PR Exit Extproc('exit') ;
*N uns(3:0) value ;
End-PR;
Dcl-S indPtr pointer inz( %addr(*in) ) ;
Dcl-DS indicators len(99) based( indPtr ) ;
overFlowLine ind pos(73) ;
End-DS ;
Dcl-DS textRecords Occurs(366);
trnDate zoned(8:0) ;
unIndexed int(10) ;
fileName char(8) ;
END-DS;
Dcl-DS imageRecords Occurs(700);
idxDte char(10) ;
idxRec int(10) ;
csgName char(40) ;
END-DS;
Dcl-C SQLstsOK '00000' ;
Dcl-C SQLstsEOF '02000' ;
Dcl-S rows int(10) ;
Dcl-S rowCount int(10) ;
Dcl-S x int(10) ;
Dcl-S billable zoned(9:2) ;
Dcl-S records int(10) ;
Dcl-S firstDate date ;
Dcl-S lastDate date ;
Dcl-S dateToday date ;
Dcl-S printDate date ;
Dcl-S fromDate zoned(8:0) ;
Dcl-S toDate zoned(8:0) ;
Dcl-S printJob char(8) ;
Exec SQL
set option
Commit = *None,
Naming = *Sys,
DECRESULT = (63, 63, 0),
datfmt = *ISO ;
Dcl-Proc CSG078RP ;
Dcl-PI *N extPgm('CSG078RP') ;
sysPrint char(2) const ;
startDate char(8) const options(*NoPASS) ;
EndDate char(8) const options(*NoPASS) ;
END-PI;
Monitor ;
If %parms < 3 ;
firstDate = %date() ;
lastDate = firstDate + %days(1) ;
Else ;
firstDate = %date(startDate:*ISO0) ;
lastDate = %date(endDate:*ISO0) ;
Endif ;
On-error ;
exit(0) ;
EndMon ;
fromDate = %dec(firstDate:*ISO) ;
toDate = %dec(lastDate:*ISO) ;
dateToday= %date() ;
Clear *all TextRecords ;
rowCount = 0 ;
records = 0 ;
Exec SQL Close CSG078c1 ;
//* create cursor for report
Exec SQL
Declare CSG078c1 insensitive cursor for
( SELECT prtdate, count(*), substr(prpdffil,1,8)
FROM csglogpf a Left join csgpompf b on A.PRGUID = B.PMGUID
Where prtdate between :fromDate and :toDate
and prpdffil
like '%' concat :sysPrint concat '%' and b.pmguid is null
Group by substr(prpdffil,1,8), prtdate
Order by prtdate, substr(prpdffil,1,8) ) ;
Exec SQL Open CSG078c1 ;
EXEC SQL
get diagnostics :rows = db2_number_rows;
If not %open(CSG078pr) ;
open CSG078pr ;
EndIf ;
Select ;
When sysPrint = 'BB' ;
mclass = 'Bulk Certificate' ;
When sysPrint = 'BC' ;
mclass = 'Proof of Mail' ;
When sysPrint = 'BE' ;
mclass = 'Certified' ;
EndSl ;
write CSG078h1 ; // Write title line
write CSG078h2 ; // Write report heading
If rows > 0 ;
Exec SQL fetch CSG078c1 for :rows rows into :textRecords ;
Exec SQL Close CSG078c1 ;
totDue = 0 ;
For x = 1 to rows ;
%occur(textRecords) = x ;
missing = unIndexed ;
printDate = %date(trnDate:*ISO) ;
ovrDue = %Diff(dateToday: printDate: *Days) ;
misstot += unIndexed ;
If overFlowLine ;
write CSG078H1 ;
write CSG078H2 ;
overFlowLine = *Off ;
Endif ;
write CSG078d1 ;
If ovrDue > 3 ;
totDue += 1 ;
Endif ;
EndFor ;
EndIf;
write CSG078t1 ; // Write total line
//********************************************************************
//* Missing image section *
//********************************************************************
Select ;
When sysPrint = 'BB' ;
printJob = '76400010' ;
When sysPrint = 'BC' ;
printJob = '76400015' ;
When sysPrint = 'BE' ;
printJob = '76400025' ;
EndSl ;
Clear *all imageRecords ;
rowCount = 0 ;
records = 0 ;
overFlowline = *On ;
Exec SQL Close CSG078c2 ;
Exec SQL
Declare CSG078c2 insensitive cursor for
( SELECT pmpdte, count(*), substring(pmfile,1,40) as CSGNAME
FROM csgpompf
where pmpdte between :startDate and :endDate
and pmindx <> 'Y'
and pmfile like '%' concat :printJob concat '%'
Group by pmfile,pmpdte
Order by pmpdte ) ;
Exec SQL Open CSG078c2 ;
EXEC SQL
get diagnostics :rows = db2_number_rows;
write CSG078h1 ; // Write title line
write CSG078h2 ; // Write report heading
If rows > 0 ;
Exec SQL fetch CSG078c2 for :rows rows into :imageRecords ;
Exec SQL Close CSG078c2 ;
totDue = 0 ;
For x = 1 to rows ;
%occur(imageRecords) = x ;
printDate = %date(idxDte:*ISO0) ;
idxDte = %char(printDate:*ISO) ;
pstDue = %Diff(dateToday: printDate: *Days) ;
idxCnt = idxRec ;
idxtot += idxCnt ;
If overFlowLine ;
write CSG078H1 ;
write CSG078H3 ;
overFlowLine = *Off ;
Endif ;
write CSG078d2 ;
imgCnt += 1 ;
EndFor ;
EndIf;
write CSG078t2 ; // Write total line
IF %open(CSG078pr) ;
close CSG078pr ;
EndIf ;
*InLR = *On;
exit(0) ;
Return ;
End-Proc CSG078RP ;