Embedded SQL Exampleè Using 2 sequential cursors
Physical
file used in the program: PARTYD
PNAME PNUM PADDR OPNDD OPNMM OPNYY OPNSTS
000001 UPI P00000000001 CHENNAI 1 1 2,010 2
000002 SHRI P00000000002 EMPHAL 2 3 2,009 2
000003 SHMANT P00000000003 CHENNAI 3 5 2,011 2
000004 AFTAB P00000000007 HOMEIIEA 13 1 2,010 2
000005 AMI P00000000008 HELLY CORN 12 12 2,012 2
000006 JPY P00000000009 JEKINSAN 11 11 2,011 2
000007 SHANN P00000000010 KUPUNUS 24 1 2,012 2
000008 HAPART P00000000011 KOLINSA 16 1 2,010 2
****** ******** End of report ********
Physical
file used in the program: ACCSUBPF
ORG CODE ACC NUMBER CURRENCY ACCOUNT MNG COUNTRY PARTY
190 A00000000001 EUR DFJASHN NKDAHFJ P00000000001
191 A00000000002 USD DAJFK DAJH P00000000001
192 A00000000003 TRY DAHB BJDKFA P00000000001
190 A00000000011 EUR NDJA CINE P00000000002
193 A00000000004 USD KIRAN NDKAL P00000000001
194 A00000000005 KWR DANK NKD P00000000001
190 A00000000001 USD MING CHINA P00000000003
191 A00000000002 TRY JHINGA CHINA P00000000003
190 A00000000021 USD DFKAF NKDA P00000000002
190 A00000000011 EUR NDJA CINE P00000000002
190 A00000000021 USD DFKAF NKDA P00000000002
190 A00000000001 USD MING CHINA P00000000003
191 A00000000002 TRY JHINGA CHINA P00000000003
190 A00000000001 EUR DFAKLSJN NDOAF P00000000007
191 A00000000002 USD KJDANFK NKDA P00000000007
190 A00000000001 EUR DFAKLSJN NDOAF P00000000007
191 A00000000002 USD KJDANFK NKDA P00000000007
195 A00000000006 TRY APPRINA SOUTN P00000000001
195 A00000000007 USD SONATNA SOUTN P00000000001
Display
file used in the program: EMBED_DSP
Columns . . . : 1 80 Browse AMIT/QRPGLESRC
SEU==> EMBED_DSP
FMT DP .....AAN01N02N03T.Name++++++RLen++TDpBLinPosFunctions+++++++++++++++++++++++++++
************ Beginning of data *********************************************************
0001.00 A DSPSIZ(24 80 *DS3)
0002.00 A CA03(03 'EXIT')
0003.00 A R EMBED_SFL SFL
0004.00 A S_PNUM 12A O 7 13
0005.00 A S_PNAME 12A O 7 35
0006.00 A S_COUNT 2S 0O 7 58
0007.00 A R EMBED_CTL SFLCTL(EMBED_SFL)
0007.01 A OVERLAY
0008.00 A 42 SFLDSP
0009.00 A 41 SFLDSPCTL
0010.00 A 40 SFLCLR
0011.00 A 45 SFLEND(*MORE)
0012.00 A SFLSIZ(0011)
0013.00 A SFLPAG(0010)
0014.00 A FIRSTRRN 4S 0H SFLRCDNBR(CURSOR)
0015.00 A 1 28'PARTY ACCOUNT INFORMATION'
0016.00 A 1 73DATE
0017.00 A EDTCDE(Y)
0018.00 A 2 73TIME
0019.00 A 6 13'PARTY NUMBER'
0020.00 A DSPATR(HI)
0021.00 A 6 36'PARTY NAME'
0022.00 A DSPATR(HI)
0023.00 A 6 52'NO. OF ACCOUNTS'
0024.00 A DSPATR(HI)
0025.00 A R FOOTER
0026.00 A 24 3'F3 = EXIT'
****************** End of data *********************************************************
PROGAM
*************** Beginning of data *************************************
HDEBUG(*YES)
HOPTION(*NODEBUGIO)
FEMBED_DSP CF E WORKSTN
F SFILE(EMBED_SFL:RRN)
F INFDS(infds)
*
Dinfds DS
DRECNO 378 379I 0
*
DARR S 10 DIM(9)
D PERRCD(3)
D CTDATA
*
DW_PNUM1 S 12A INZ(*BLANKS)
DW_PNUM2 S 12A INZ(*BLANKS)
C *IN03 DOWEQ *OFF
C 03 LEAVE
C EXSR DSPSFL
C ENDDO
C SETON LR
C*
C*_________________________________________________
C*_______________CLEAR SUBFILE_____________________
C*_________________________________________________
C CLRSFL BEGSR
C EVAL RRN=0
C SETON 40
C WRITE EMBED_CTL
C SETOFF 40
C ENDSR
C*_________________________________________________
C*_______________FIRST FUBROUTINE__________________
C*_________________________________________________
C*
C *INZSR BEGSR
C SETOFF 404142
C SETOFF 45
C Z-ADD *ZEROS RRN 4 0
C EVAL FIRSTRRN=1
C EXSR CLRSFL
C EXSR FILSFL
C EXSR DSPSFL
C ENDSR
C*_________________________________________________
C*_______________FILL SUBFILE__________________
C*_________________________________________________
C*
C FILSFL BEGSR
C
C EVAL RRN=0
*
C/EXEC SQL
C+ DECLARE C1 CURSOR FOR select count(*), PARTY from AMIT/ACCSUBPF
C+ group by PARTY order by PARTY
C/END-EXEC
C/EXEC SQL
C+ DECLARE C2 CURSOR FOR select PNAME, PNUM from AMIT/PARTYD
C+ order by PNUM
C/END-EXEC
*
*
C/EXEC SQL
C+ OPEN C1
C/END-EXEC
*
C/EXEC SQL
C+ OPEN C2
C/END-EXEC
*
C DOW SQLCOD=0
C/EXEC SQL
C+ FETCH FROM C1 INTO :S_COUNT, :W_PNUM1
C/END-EXEC
C/EXEC SQL
C+ FETCH FROM C2 INTO :S_PNAME, :W_PNUM2
C/END-EXEC
C IF SQLCOD<0
C LEAVE
C ENDIF
C IF SQLCOD=100
C SETON 45
C LEAVE
C ENDIF
C IF W_PNUM1<>W_PNUM2
C ITER
C ENDIF
C EVAL RRN=RRN+1
C IF RRN>9999
C LEAVE
C ENDIF
C EVAL S_PNUM=W_PNUM1
C WRITE EMBED_SFL
C ENDDO
*
C/EXEC SQL
C+ CLOSE C1
C/END-EXEC
*
C/EXEC SQL
C+ CLOSE C2
C/END-EXEC
C ENDSR
C*_________________________________________________
C*
C DSPSFL BEGSR
C SETON 4142
C IF RRN<=0
C SETOFF 42
C ENDIF
C WRITE FOOTER
C EXFMT EMBED_CTL
C EVAL FIRSTRRN=RECNO
C SETOFF 4142
C ENDSR
****************** End of data ****************************************
OUTPUT
PARTY ACCOUNT INFORMATION 2/14/13
03:54:22
PARTY NUMBER PARTY NAME NO. OF ACCOUNTS
P00000000001 UPI 07
P00000000002 SHRI 04
P00000000003 SHMANT 04
P00000000007 AFTAB 04
Bottom
F3 = EXIT