Multiple Occurrence DS Use
Processing a multiple occurrence data structure.
In this example, the get diagnostics function of SQL is used to determine the number of rows there are to process. The FETCH then returns all the rows into a multiple occurrence data structure in a single FETCH. The data structure is then processed, using a FOR loop to move through the occurences.
**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 ;