Embedded SQL Example
è Make dynamic query by making a Querystring, PART-II
Physical
file used in the program: PARTYD
PNAME PNUM PADDR OPNDD OPNMM OPNYY OPNSTS
000001 UPI P00001 CHENNAI 1 1 2,010 2
000002 SHRI P00002 EMPHAL 2 3 2,009 2
000003 SHMANT P00003 CHENNAI 3 5 2,011 2
000004 AFTAB P00007 HOMEIIEA 13 1 2,010 2
000005 AMI P00008 HELLY CORN 12 12 2,012 2
000006 JPY P00009 JEKINSAN 11 11 2,011 2
000007 SHANN P00010 KUPUNUS 24 1 2,012 2
000008 HAPART P00011 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 A00001 EUR DFJASHN NKDAHFJ P00001
191 A00002 USD DAJFK DAJH P00001
192 A00003 TRY DAHB BJDKFA P00001
190 A00011 EUR NDJA CINE P00002
193 A00004 USD KIRAN NDKAL P00001
194 A00005 KWR DANK NKD P00001
190 A00001 USD MING CHINA P00003
191 A00002 TRY JHINGA CHINA P00003
190 A00021 USD DFKAF NKDA P00002
190 A00011 EUR NDJA CINE P00002
190 A00021 USD DFKAF NKDA P00002
190 A00001 USD MING CHINA P00003
191 A00002 TRY JHINGA CHINA P00003
190 A00001 EUR DFAKLSJN NDOAF P00007
191 A00002 USD KJDANFK NKDA P00007
190 A00001 EUR DFAKLSJN NDOAF P00007
191 A00002 USD KJDANFK NKDA P00007
195 A00006 TRY APPRINA SOUTN P00001
195 A00007 USD SONATNA SOUTN P00001
Physical
file used in the program: RESULT1
Columns . . . : 1 80 Browse AMIT/QRPGLESRC
SEU==> RESULT1
FMT PF .....A..........T.Name++++++RLen++TDpB......Functions+++++++++++++++++++++++++++
*************** Beginning of data **********************************************************
0001.00 A R RES0001 130215
0002.00 A PARTY_NO 12A 130215
0003.00 A COUNT 2P 0 130215
0004.00 A K PARTY_NO 130215
****************** End of data ************************************************************
Physical
file used in the program: RESULT2
Columns . . . : 1 80 Browse AMIT/QRPGLESRC
SEU==> RESULT2
FMT PF .....A..........T.Name++++++RLen++TDpB......Functions+++++++++++++++++++++++++++
*************** Beginning of data *****************************************************************
0001.00 A R RES0002 130215
0002.00 A PARTY_NAME 12A 130215
0003.00 A PARTY_NUM 12A 130215
0004.00 A K PARTY_NUM 130215
****************** End of data ********************************************************************
Logical
file used in the program: RESULTL
Columns . . . : 1 80 Edit AMIT/QRPGLESRC
SEU==> RESULTL
FMT LF .....A..........T.Name++++++.Len++TDpB......Functions+++++++++++++++++++++++++++
*************** Beginning of data *****************************************************************
0001.00 A R RES JFILE(RESULT1 RESULT2) 130215
0002.00 A J JOIN(RESULT1 RESULT2) 130215
0003.00 A JFLD(PARTY_NO PARTY_NUM) 130215
0004.00 A PARTY_NO JREF(1) 130215
0005.00 A COUNT JREF(1) 130215
0006.00 A PARTY_NAME JREF(2) 130215
0007.00 A K PARTY_NO 130215
****************** End of data ********************************************************************
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) 130215
0002.00 A CA03(03 'EXIT') 130215
0003.00 A R EMBED_SFL SFL 130215
0004.00 A S_PNUM 12A O 7 13 130215
0005.00 A S_PNAME 12A O 7 35 130215
0006.00 A S_COUNT 2S 0O 7 58 130215
0007.00 A R EMBED_CTL SFLCTL(EMBED_SFL) 130215
0007.01 A OVERLAY 130215
0008.00 A 42 SFLDSP 130215
0009.00 A 41 SFLDSPCTL 130215
0010.00 A 40 SFLCLR 130215
0011.00 A 45 SFLEND(*MORE) 130215
0012.00 A SFLSIZ(0011) 130215
0013.00 A SFLPAG(0010) 130215
0014.00 A FIRSTRRN 4S 0H SFLRCDNBR(CURSOR) 130215
0015.00 A 1 28'PARTY ACCOUNT INFORMATION' 130215
0016.00 A 1 73DATE 130215
0017.00 A EDTCDE(Y) 130215
0018.00 A 2 73TIME 130215
0019.00 A 6 13'PARTY NUMBER' 130215
0020.00 A DSPATR(HI) 130215
0021.00 A 6 36'PARTY NAME' 130215
0022.00 A DSPATR(HI) 130215
0023.00 A 6 52'NO. OF ACCOUNTS' 130215
0024.00 A DSPATR(HI) 130215
0025.00 A R FOOTER 130215
0027.00 A 24 3'F3 = EXIT' 130215
****************** End of data ************************************************************
PROGRAM
Columns . . . : 1 80 Edit AMIT/QRPGLESRC
SEU==> EMBED_STM2
FMT H .....HKeywords++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
*************** Beginning of data *********************************************************
0001.00 HDEBUG(*YES)
0002.00 HOPTION(*NODEBUGIO)
0003.00 FRESULTL IF E K DISK
0004.00 FEMBED_DSP CF E WORKSTN
0005.00 F SFILE(EMBED_SFL:RRN)
0006.00 F INFDS(infds)
0007.00 *
0008.00 Dinfds DS
0009.00 DRECNO 378 379I 0
0010.00 *
0011.00 DSQLSTMT1 S 350
0012.00 DSQLSTMT2 S 350
0013.00 DARR S 10 DIM(9)
0014.00 D PERRCD(3)
0015.00 D CTDATA
0016.00 *
0017.00 DW_PNUM1 S 12A INZ(*BLANKS)
0018.00 DW_PNUM2 S 12A INZ(*BLANKS)
0019.00 C *IN03 DOWEQ *OFF
0020.00 C 03 LEAVE
0021.00 C EXSR DSPSFL
0022.00 C ENDDO
0023.00 C SETON LR
0024.00 C*
0025.00 C*_________________________________________________
0026.00 C*_______________CLEAR SUBFILE_____________________
0027.00 C*_________________________________________________
0028.00 C CLRSFL BEGSR
0029.00 C EVAL RRN=0
0030.00 C SETON 40
0031.00 C WRITE EMBED_CTL
0032.00 C SETOFF 40
0033.00 C ENDSR
0034.00 C*_________________________________________________
0035.00 C*_______________FIRST FUBROUTINE__________________
0036.00 C*_________________________________________________
0037.00 C*
0038.00 C *INZSR BEGSR
0039.00 C SETOFF 404142
0040.00 C SETOFF 45
0041.00 C Z-ADD *ZEROS RRN 4 0
0042.00 C EVAL FIRSTRRN=1
0043.00 C EXSR CLRSFL
0044.00 C EXSR FILSFL
0045.00 C EXSR DSPSFL
0046.00 C ENDSR
0047.00 C*_________________________________________________
0048.00 C*_______________FILL SUBFILE__________________
0049.00 C*_________________________________________________
0050.00 C*
0051.00 C FILSFL BEGSR
0052.00 C EVAL SQLSTMT1= 'INSERT INTO RESULT1('+
0053.00 C '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 ')'
0058.00 C EVAL SQLSTMT2= 'INSERT INTO RESULT2('+
0059.00 C 'SELECT DISTINCT ' +
0060.00 C ARR(8) + ', ' + ARR(9) +
0061.00 C ' FROM ' +
0062.00 C ARR(1) + '/' + ARR(5) +
0063.00 C ')'
0064.00 C EVAL RRN=0
0065.00 *
0066.00 C/EXEC SQL
0067.00 C+ SET OPTION COMMIT=*NONE
0068.00 C/END-EXEC
0069.00 *
0070.00 C/EXEC SQL
0071.00 C+ EXECUTE IMMEDIATE :SQLSTMT1
0072.00 C/END-EXEC
0073.00 C/EXEC SQL
0074.00 C+ EXECUTE IMMEDIATE :SQLSTMT2
0075.00 C/END-EXEC
0076.00 *
0077.00 C IF SQLCOD<0
0078.00 C SETON LR
0079.00 C RETURN
0080.00 C ENDIF
0081.00 C IF SQLCOD=100
0082.00 C SETON 45
0083.00 C ENDIF
0084.00 *
0085.00 C IF SQLCOD=0
0086.00 C *LOVAL SETLL RES
0087.00 C READ(N) RES
0088.00 C DOW NOT %EOF(RESULTL)
0089.00 C EVAL RRN=RRN+1
0090.00 C IF RRN>9999
0091.00 C LEAVE
0092.00 C ENDIF
0093.00 C EVAL S_PNUM=PARTY_NO
0094.00 C EVAL S_PNAME=PARTY_NAME
0095.00 C EVAL S_COUNT=COUNT
0096.00 C WRITE EMBED_SFL
0097.00 C READ RES
0098.00 C ENDDO
0099.00 *
0100.00 C IF %EOF(RESULTL)
0101.00 C SETON 45
0102.00 C ENDIF
0103.00 *
0104.00 C ENDIF
0105.00 *
0106.00 C/EXEC SQL
0107.00 C+ DELETE FROM AMIT/RESULT1
0108.00 C/END-EXEC
0109.00 *
0110.00 C/EXEC SQL
0111.00 C+ DELETE FROM AMIT/RESULT2
0112.00 C/END-EXEC
0113.00 C ENDSR
0114.00 C*_________________________________________________
0115.00 C*
0116.00 C DSPSFL BEGSR
0117.00 C SETON 4142
0118.00 C IF RRN<0
0119.00 C SETOFF 42
0120.00 C ENDIF
0121.00 C WRITE FOOTER
0122.00 C EXFMT EMBED_CTL
0123.00 C EVAL FIRSTRRN=RECNO
0124.00 C SETOFF 4142
0125.00 C ENDSR
0126.00 **CTDATA ARR
0127.00 AMIT
0128.00 ACCSUBPF PARTYD
0129.00 PARTY PNAME PNUM
****************** End of data ***********************************************************
OUTPUT
PARTY ACCOUNT INFORMATION 2/21/13
00:58:14
PARTY NUMBER PARTY NAME NO. OF ACCOUNTS
P00001 IROBO 07
P00002 KEMON 04
P00003 SHMANT 04
P00007 AFTAB 04
Bottom
F3 = EXIT