Ü Embedded SQL using SQLSTMT
without cursor
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
0002.00 A PARTY_NO 12A
0003.00 A COUNT 2P 0
0004.00 A K PARTY_NO
****************** 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
0002.00 A PARTY_NAME 12A
0003.00 A PARTY_NUM 12A
0004.00 A K PARTY_NUM
****************** 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)
0002.00 A J JOIN(RESULT1 RESULT2)
0003.00 A JFLD(PARTY_NO PARTY_NUM)
0004.00 A PARTY_NO JREF(1)
0005.00 A COUNT JREF(1)
0006.00 A PARTY_NAME JREF(2)
0007.00 A K PARTY_NO
****************** 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)
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 ************************************************************
Program
demonstrating Embedded SQL using SQLSTMT without cursor
Columns . . . : 1 80 Browse AMIT/QRPGLESRC
SEU==> EMBED_STM2
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 FRESULTL IF E K DISK
0006.00 FEMBED_DSP CF E WORKSTN
0007.00 F SFILE(EMBED_SFL:RRN)
0008.00 F INFDS(infds)
0009.00 *
0010.00 Dinfds DS
0011.00 DRECNO 378 379I 0
0012.00 *
0013.00 DSQLSTMT1 S 350
0014.00 DSQLSTMT2 S 350
0015.00 DARR S 10 DIM(9)
0016.00 D PERRCD(3)
0017.00 D CTDATA
0018.00 *
0019.00 DW_PNUM1 S 12A INZ(*BLANKS)
0020.00 DW_PNUM2 S 12A INZ(*BLANKS)
0021.00 C *IN03 DOWEQ *OFF
0022.00 C 03 LEAVE
0023.00 C EXSR DSPSFL
0024.00 C ENDDO
0025.00 C SETON LR
0026.00 C*
0027.00 C*_________________________________________________
0028.00 C*_______________CLEAR SUBFILE_____________________
0029.00 C*_________________________________________________
0030.00 C CLRSFL BEGSR
0031.00 C EVAL RRN=0
0032.00 C SETON 40
0033.00 C WRITE EMBED_CTL
0034.00 C SETOFF 40
0035.00 C ENDSR
0036.00 C*_________________________________________________
0037.00 C*_______________FIRST FUBROUTINE__________________
0038.00 C*_________________________________________________
0039.00 C*
0040.00 C *INZSR BEGSR
0041.00 C SETOFF 404142
0042.00 C SETOFF 45
0043.00 C Z-ADD *ZEROS RRN 4 0
0044.00 C EVAL FIRSTRRN=1
0045.00 C EXSR CLRSFL
0046.00 C EXSR FILSFL
0047.00 C EXSR DSPSFL
0048.00 C ENDSR
0049.00 C*_________________________________________________
0050.00 C*_______________FILL SUBFILE__________________
0051.00 C*_________________________________________________
0052.00 C*
0053.00 C FILSFL BEGSR
0054.00 C EVAL SQLSTMT1= 'INSERT INTO RESULT1('+
0055.00 C 'SELECT COUNT(*), ' +
0056.00 C ARR(7) + ' FROM ' +
0057.00 C ARR(1) + '/' + ARR(4) +
0058.00 C ' GROUP BY '+ ARR(7) +
0059.00 C ')'
0060.00 C EVAL SQLSTMT2= 'INSERT INTO RESULT2('+
0061.00 C 'SELECT DISTINCT ' +
0062.00 C ARR(8) + ', ' + ARR(9) +
0063.00 C ' FROM ' +
0064.00 C ARR(1) + '/' + ARR(5) +
0065.00 C ')'
0066.00 C EVAL RRN=0
0067.00 *
0068.00 C/EXEC SQL
0069.00 C+ SET OPTION COMMIT=*NONE
0070.00 C/END-EXEC
0071.00 *
0072.00 C/EXEC SQL
0073.00 C+ EXECUTE IMMEDIATE :SQLSTMT1
0074.00 C/END-EXEC
0075.00 C/EXEC SQL
0076.00 C+ EXECUTE IMMEDIATE :SQLSTMT2
0077.00 C/END-EXEC
0078.00 *
0079.00 C IF SQLCOD<>0
0080.00 C IF SQLCOD=100
0081.00 C SETON 45
0082.00 C ENDIF
0083.00 C SETON LR
0084.00 C RETURN
0085.00 C ENDIF
0086.00 *
0087.00 C IF SQLCOD=0
0088.00 C *LOVAL SETLL RES
0089.00 C READ(N) RES
0090.00 C DOW NOT %EOF(RESULTL)
0091.00 C EVAL RRN=RRN+1
0092.00 C EVAL S_PNUM=PARTY_NO
0093.00 C EVAL S_PNAME=PARTY_NAME
0094.00 C EVAL S_COUNT=COUNT
0095.00 C WRITE EMBED_SFL
0096.00 C READ RES
0097.00 C ENDDO
0098.00 *
0099.00 C IF %EOF(RESULTL)
0100.00 C SETON 45
0101.00 C ENDIF
0102.00 *
0103.00 C ENDIF
0104.00 *
0105.00 C/EXEC SQL
0106.00 C+ DELETE FROM AMIT/RESULT1
0107.00 C/END-EXEC
0108.00 *
0109.00 C/EXEC SQL
0110.00 C+ DELETE FROM AMIT/RESULT2
0111.00 C/END-EXEC
0112.00 C ENDSR
0113.00 C*_________________________________________________
0114.00 C*
0115.00 C DSPSFL BEGSR
0116.00 C SETON 4142
0117.00 C WRITE EMBED_CTL
0118.00 C WRITE FOOTER
0119.00 C READ EMBED_CTL
0120.00 C EVAL FIRSTRRN=RECNO
0121.00 C SETOFF 4142
0122.00 C ENDSR
0123.00 **CTDATA ARR
0124.00 AMIT
0125.00 ACCSUBPF PARTYD
0126.00 PARTY PNAME PNUM
****************** End of data ***********************************************************
OUTPUT
PARTY ACCOUNT INFORMATION 2/14/13
05:42:09
PARTY NUMBER PARTY NAME NO. OF ACCOUNTS
P00000000001 UPI 07
P00000000002 SHRI 04
P00000000003 SHMANT 04
P00000000007 AFTAB 02
Bottom
F3 = EXIT