Embedded SQL Example èMake dynamic query by
making a Querystring, PART-I
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) 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 ************************************************************
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 FEMBED_DSP CF E WORKSTN
0004.00 F SFILE(EMBED_SFL:RRN)
0005.00 F INFDS(infds)
0006.00 *
0007.00 Dinfds DS
0008.00 DRECNO 378 379I 0
0009.00 *
0010.00 DSQLSTMT1 S 350
0011.00 DSQLSTMT2 S 350
0012.00 DARR S 10 DIM(9)
0013.00 D PERRCD(3)
0014.00 D CTDATA
0015.00 *
0016.00 DW_PNUM1 S 12A INZ(*BLANKS)
0017.00 DW_PNUM2 S 12A INZ(*BLANKS)
0018.00 C *IN03 DOWEQ *OFF
0019.00 C 03 LEAVE
0020.00 C EXSR DSPSFL
0021.00 C ENDDO
0022.00 C SETON LR
0023.00 C*_________________________________________________
0024.00 C*_______________CLEAR SUBFILE_____________________
0025.00 C*_________________________________________________
0026.00 C CLRSFL BEGSR
0027.00 C EVAL RRN=0
0028.00 C SETON 40
0029.00 C WRITE EMBED_CTL
0030.00 C SETOFF 40
0031.00 C ENDSR
0032.00 C*_________________________________________________
0033.00 C*_______________FIRST FUBROUTINE__________________
0034.00 C*_________________________________________________
0035.00 C*
0036.00 C *INZSR BEGSR
0037.00 C SETOFF 404142
0038.00 C SETOFF 45
0039.00 C Z-ADD *ZEROS RRN 4 0
0040.00 C EVAL FIRSTRRN=1
0041.00 C EXSR CLRSFL
0042.00 C EXSR FILSFL
0043.00 C EXSR DSPSFL
0044.00 C ENDSR
0045.00 C*_________________________________________________
0046.00 C*_______________FILL SUBFILE__________________
0047.00 C*_________________________________________________
0048.00 C*
0049.00 C FILSFL BEGSR
0050.00 C EVAL SQLSTMT1= 'SELECT COUNT(*), ' +
0051.00 C ARR(7) + ' FROM ' +
0052.00 C ARR(1) + '/' + ARR(4) +
0053.00 C ' GROUP BY '+ ARR(7) +
0054.00 C ' ORDER BY ' + ARR(7)
0055.00 C EVAL SQLSTMT2= 'SELECT DISTINCT ' +
0056.00 C ARR(8) + ', ' + ARR(9) +
0057.00 C ' FROM ' +
0058.00 C ARR(1) + '/' + ARR(5) +
0059.00 C ' ORDER BY ' + ARR(9)
0060.00 C EVAL RRN=0
0061.00 *
0062.00 C/EXEC SQL
0063.00 C+ PREPARE stmt1 FROM :SQLSTMT1
0064.00 C/END-EXEC
0065.00
0066.00 C/EXEC SQL
0067.00 C+ PREPARE stmt2 FROM :SQLSTMT2
0068.00 C/END-EXEC
0069.00 *
0070.00 ***
0071.00 C/EXEC SQL
0072.00 C+ DECLARE C1 CURSOR FOR stmt1
0073.00 C/END-EXEC
0074.00 C/EXEC SQL
0075.00 C+ DECLARE C2 CURSOR FOR stmt2
0076.00 C/END-EXEC
0077.00 *
0078.00 *
0079.00 C/EXEC SQL
0080.00 C+ OPEN C1
0081.00 C/END-EXEC
0082.00 *
0083.00 C/EXEC SQL
0084.00 C+ OPEN C2
0085.00 C/END-EXEC
0086.00 *
0087.00 C DOW SQLCOD=0
0088.00 C/EXEC SQL
0089.00 C+ FETCH FROM C1 INTO :S_COUNT, :W_PNUM1
0090.00 C/END-EXEC
0091.00 C/EXEC SQL
0092.00 C+ FETCH FROM C2 INTO :S_PNAME, :W_PNUM2
0093.00 C/END-EXEC
0094.00 C IF SQLCOD<0
0095.00 C LEAVE
0096.00 C ENDIF
0097.00 C IF SQLCOD=100
0098.00 C SETON 45
0099.00 C LEAVE
0100.00 C
0101.00 C ENDIF
0102.00 C IF W_PNUM1<>W_PNUM2
0103.00 C ITER
0104.00 C ENDIF
0105.00 C EVAL RRN=RRN+1
0106.00 C IF RRN>9999
0107.00 C LEAVE
0108.00 C ENDIF
0109.00 C EVAL S_PNUM=W_PNUM1
0110.00 C WRITE EMBED_SFL
0111.00 C ENDDO
0112.00 *
0113.00 C/EXEC SQL
0114.00 C+ CLOSE C1
0115.00 C/END-EXEC
0116.00 *
0117.00 C/EXEC SQL
0118.00 C+ CLOSE C2
0119.00 C/END-EXEC
0120.00 C ENDSR
0121.00 C*_________________________________________________
0122.00 C*
0123.00 C DSPSFL BEGSR
0124.00 C SETON 4142
0125.00 C IF RRN<=0
0126.00 C SETOFF 42
0127.00 C ENDIF
0128.00 C WRITE FOOTER
0129.00 C WRITE EMBED_CTL
0130.00 C READ EMBED_CTL
0131.00 C EVAL FIRSTRRN=RECNO
0132.00 C SETOFF 4142
0133.00 C ENDSR
0134.00 **CTDATA ARR
0135.00 AMIT
0136.00 ACCSUBPF PARTYD
0137.00 PARTY PNAME PNUM
****************** End of data ***************************************************
OUTPUT
In the program above the query-string generated will be:
select count(*), PARTY from
AMIT/ACCSUBPF group by party order by party
select PNAME, PNUM FROM AMIT/PARTYD
ORDER BY PNUM
PARTY ACCOUNT INFORMATION 2/21/13
00:48:00
PARTY NUMBER PARTY NAME NO. OF ACCOUNTS
P00000000001 IROBO 07
P00000000002 KEMON 04
P00000000003 SHMANT 04
P00000000007 AFTAB 04
Bottom
F3 = EXIT
***NOTE: While making the query string if something you want to put in single quotes
then use the example given below:
e.g
Querystring for the query statement: SELECT * FTOM PARTYD WHERE PNUM=’P00000000001’ AND
PNAME LIKE ‘%A%’ will be:
C EVAL PNUM001=’P00000000001’
C EVAL PNAME001=’A ’
C EVAL COND_1='PARTY = ' + ''''+PNUM001+''''
C EVAL COND_2='PNAME LIKE'+'''%' + %TRIM(PNAME001) + '%'''
C EVAL QUERYSTRING = 'SELECT * FTOM PARTYD WHERE' +
C COND_1 + ' AND ' + COND_1