Ü Embedded SQL using cursor and SQLSTMT
1st
Physical file used in the program = PARTYD
PNAME PNUM PADDR OPNDD OPNMM OPNYY OPNSTS
UPI P00000000001 CHENNAI 1 1 2,010 2
SHRI P00000000002 EMPHAL 2 3 2,009 2
SHMANT P00000000003 CHENNAI 3 5 2,011 2
AFTAB P00000000007 HOMEIIEA 13 1 2,010 2
AMI P00000000008 HELLY CORN 12 12 2,012 2
JPY P00000000009 JEKINSAN 11 11 2,011 2
SHANN P00000000010 KUPUNUS 24 1 2,012 2
HAPART P00000000011 KOLINSA 16 1 2,010 2
2nd
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)
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 OVERLAY
0027.00 A 24 3'F3 = EXIT'
****************** End of data ********************************************************
Columns . . . : 1 80 Edit AMIT/QRPGLESRC
SEU==> EMBED_STMT
FMT H .....HKeywords++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
*************** Beginning of data ***********************************************
0001.00 HDEBUG(*YES)
0002.00 HOPTION(*NODEBUGIO)
0003.00 FPARTYD IF E K DISK
0004.00 FACCSUBPF IF E K DISK
0005.00 FEMBED_DSP CF E WORKSTN
0006.00 F SFILE(EMBED_SFL:RRN)
0007.00 F INFDS(infds)
0008.00 *
0009.00 Dinfds DS
0010.00 DRECNO 378 379I 0
0011.00 *
0012.00 DSQLSTMT1 S 350
0013.00 DSQLSTMT2 S 350
0014.00 DARR S 10 DIM(9)
0015.00 D PERRCD(3)
0016.00 D CTDATA
0017.00 *
0018.00 DW_PNUM1 S 12A INZ(*BLANKS)
0019.00 DW_PNUM2 S 12A INZ(*BLANKS)
0020.00 C *IN03 DOWEQ *OFF
0021.00 C 03 LEAVE
0022.00 C EXSR DSPSFL
0023.00 C ENDDO
0024.00 C SETON LR
0025.00 C*
0026.00 C*_________________________________________________
0027.00 C*_______________CLEAR SUBFILE_____________________
0028.00 C*_________________________________________________
0029.00 C CLRSFL BEGSR
0030.00 C EVAL RRN=0
0031.00 C SETON 40
0032.00 C WRITE EMBED_CTL
0033.00 C SETOFF 40
0034.00 C ENDSR
0035.00 C*_________________________________________________
0036.00 C*_______________FIRST FUBROUTINE__________________
0037.00 C*_________________________________________________
0038.00 C*
0039.00 C *INZSR BEGSR
0040.00 C SETOFF 404142
0041.00 C SETOFF 45
0042.00 C Z-ADD *ZEROS RRN 4 0
0043.00 C EVAL FIRSTRRN=1
0044.00 C EXSR CLRSFL
0045.00 C EXSR FILSFL
0046.00 C EXSR DSPSFL
0047.00 C ENDSR
0048.00 C*_________________________________________________
0049.00 C*_______________FILL SUBFILE__________________
0050.00 C*_________________________________________________
0051.00 C*
0052.00 C FILSFL BEGSR
0053.00 C EVAL SQLSTMT1= 'SELECT COUNT(*), ' +
0054.00 C ARR(7) + ' FROM ' +
0055.00 C ARR(1) + '/' + ARR(4) +
0056.00 C ' GROUP BY '+ ARR(7) +
0057.00 C ' ORDER BY ' + ARR(7)
0058.00 C EVAL SQLSTMT2= 'SELECT DISTINCT ' +
0059.00 C ARR(8) + ', ' + ARR(9) +
0060.00 C ' FROM ' +
0061.00 C ARR(1) + '/' + ARR(5) +
0062.00 C ' ORDER BY ' + ARR(9)
0063.00 C EVAL RRN=0
0064.00 *
0065.00 C/EXEC SQL
0066.00 C+ PREPARE stmt1 FROM :SQLSTMT1
0067.00 C/END-EXEC
0068.00
0069.00 C/EXEC SQL
0070.00 C+ PREPARE stmt2 FROM :SQLSTMT2
0071.00 C/END-EXEC
0072.00 *
0073.00 ***
0074.00 C/EXEC SQL
0075.00 C+ DECLARE C1 CURSOR FOR stmt1
0076.00 C/END-EXEC
0077.00 C/EXEC SQL
0078.00 C+ DECLARE C2 CURSOR FOR stmt2
0079.00 C/END-EXEC
0080.00 *
0081.00 *
0082.00 C/EXEC SQL
0083.00 C+ OPEN C1
0084.00 C/END-EXEC
0085.00 *
0086.00 C/EXEC SQL
0087.00 C+ OPEN C2
0088.00 C/END-EXEC
0089.00 *
0090.00 C DOW SQLCOD=0
0091.00 C/EXEC SQL
0092.00 C+ FETCH FROM C1 INTO :S_COUNT, :W_PNUM1
0093.00 C/END-EXEC
0094.00 C/EXEC SQL
0095.00 C+ FETCH FROM C2 INTO :S_PNAME, :W_PNUM2
0096.00 C/END-EXEC
0097.00 C IF SQLCOD<>0
0098.00 C IF SQLCOD=100
0099.00 C SETON 45
0100.00 C ENDIF
0101.00 C LEAVE
0102.00 C ENDIF
0103.00 C IF W_PNUM1<>W_PNUM2
0104.00 C ITER
0105.00 C ENDIF
0106.00 C EVAL RRN=RRN+1
0107.00 C EVAL S_PNUM=W_PNUM1
0108.00 C WRITE EMBED_SFL
0109.00 C ENDDO
0110.00 *
0111.00 C/EXEC SQL
0112.00 C+ CLOSE C1
0113.00 C/END-EXEC
0114.00 *
0115.00 C/EXEC SQL
0116.00 C+ CLOSE C2
0117.00 C/END-EXEC
0118.00 C ENDSR
0119.00 C*_________________________________________________
0120.00 C*
0121.00 C DSPSFL BEGSR
0122.00 C SETON 4142
0123.00 C WRITE EMBED_CTL
0124.00 C WRITE FOOTER
0125.00 C READ EMBED_CTL
0126.00 C EVAL FIRSTRRN=RECNO
0127.00 C SETOFF 4142
0128.00 C ENDSR
0129.00 **CTDATA ARR
0130.00 AMIT
0131.00 ACCSUBPF PARTYD
0132.00 PARTY PNAME PNUM
****************** 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