Example: SQL statements in RPG/400 programs
This example program is written in the RPG programming language.
Note: By using the code examples, you agree to the terms
of the Code license and disclaimer information.
xxxxST1 VxRxMx yymmdd Create SQL RPG Program RPGEX 08/06/07 12:55:22 Page 1
Source type...............RPG
Program name..............CORPDATA/RPGEX
Source file...............CORPDATA/SRC
Member....................RPGEX
To source file............QTEMP/QSQLTEMP
Options...................*SRC *XREF
Target release............VxRxMx
INCLUDE file..............*SRCFILE
Commit....................*CHG
Allow copy of data........*YES
Close SQL cursor..........*ENDPGM
Allow blocking............*READ
Delay PREPARE.............*NO
Generation level..........10
Printer file..............*LIBL/QSYSPRT
Date format...............*JOB
Date separator............*JOB
Time format...............*HMS
Time separator ...........*JOB
Replace...................*YES
Relational database.......*LOCAL
User .....................*CURRENT
RDB connect method........*DUW
Default collection........*NONE
Dynamic default
collection..............*NO
Package name..............*PGMLIB/*PGM
Path......................*NAMING
SQL rules.................*DB2
User profile..............*NAMING
Dynamic user profile......*USER
Sort sequence.............*JOB
Language ID...............*JOB
IBM SQL flagging..........*NOFLAG
ANS flagging..............*NONE
Text......................*SRCMBRTXT
Source file CCSID.........65535
Job CCSID.................65535
Decimal result options:
Maximum precision.......31
Maximum scale...........31
Minimum divide scale....0
DECFLOAT rounding mode....*HALFEVEN
Compiler options..........*NONE
Source member changed on 07/01/96 17:06:17
xxxxST1 VxRxMx yymmdd Create SQL RPG Program RPGEX 08/06/07 12:55:22 Page 2
Record *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 SEQNBR Last change
1 H 100
2 F* File declaration for QPRINT 200
3 F* 300
4 FQPRINT O F 132 PRINTER 400
5 I* 500
6 I* Structure for report 1. 600
7 I* 700
8 1 IRPT1 E DSPROJECT 800
9 I PROJNAME PROJNM 900
10 I RESPEMP RESEM 1000
11 I PRSTAFF STAFF 1100
12 I PRSTDATE PRSTD 1200
13 I PRENDATE PREND 1300
14 I MAJPROJ MAJPRJ 1400
15 I* 1500
16 I DS 1600
17 I 1 6 EMPNO 1700
18 I 7 36 NAME 1800
19 I P 37 412SALARY 1900
20 I* 2000
21 I* Structure for report 2. 2100
22 I* 2200
23 IRPT2 DS 2300
24 I 1 6 PRJNUM 2400
25 I 7 42 PNAME 2500
26 I B 43 440EMPCNT 2600
27 I P 45 492PRCOST 2700
28 I* 2800
29 I DS 2900
30 I B 1 20WRKDAY 3000
31 I P 3 62COMMI 3100
32 I 7 16 RDATE 3200
33 I P 17 202PERCNT 3300
34 2 C* 3400
35 C Z-ADD253 WRKDAY 3500
36 C Z-ADD2000.00 COMMI 3600
37 C Z-ADD1.04 PERCNT 3700
38 C MOVEL'1982-06-'RDATE 3800
39 C MOVE '01' RDATE 3900
40 C SETON LR 3901
41 C* 4000
42 C* Update the selected projects by the new percentage. If an 4100
43 C* error occurs during the update, roll back the changes. 4200
44 C* 4300
45 3 C/EXEC SQL WHENEVER SQLERROR GOTO UPDERR 4400
46 C/END-EXEC 4500
47 C* 4600
48 4 C/EXEC SQL 4700
49 C+ UPDATE CORPDATA/EMPLOYEE 4800
50 C+ SET SALARY = SALARY * :PERCNT 4900
51 C+ WHERE COMM >= :COMMI 5000
52 C/END-EXEC 5100
53 C* 5200
54 C* Commit changes. 5300
55 C* 5400
56 5 C/EXEC SQL COMMIT 5500
57 C/END-EXEC 5600
58 C* 5700
59 C/EXEC SQL WHENEVER SQLERROR GO TO RPTERR 5800
60 C/END-EXEC 5900
xxxxST1 VxRxMx yymmdd Create SQL RPG Program RPGEX 08/06/07 12:55:22 Page 3
Record *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 SEQNBR Last change
61 C* 6000
62 C* Report the updated statistics for each employee assigned to 6100
63 C* selected projects. 6200
64 C* 6300
65 C* Write out the header for report 1. 6400
66 C* 6500
67 C EXCPTRECA 6600
68 6 C/EXEC SQL DECLARE C1 CURSOR FOR 6700
69 C+ SELECT DISTINCT PROJNO, EMPPROJACT.EMPNO, 6800
70 C+ LASTNAME||', '||FIRSTNME, SALARY 6900
71 C+ FROM CORPDATA/EMPPROJACT, CORPDATA/EMPLOYEE 7000
72 C+ WHERE EMPPROJACT.EMPNO = EMPLOYEE.EMPNO AND 7100
73 C+ COMM >= :COMMI 7200
74 C+ ORDER BY PROJNO, EMPNO 7300
75 C/END-EXEC 7400
76 C* 7500
77 7 C/EXEC SQL 7600
78 C+ OPEN C1 7700
79 C/END-EXEC 7800
80 C* 7900
81 C* Fetch and write the rows to QPRINT. 8000
82 C* 8100
83 8 C/EXEC SQL WHENEVER NOT FOUND GO TO DONE1 8200
84 C/END-EXEC 8300
85 C SQLCOD DOUNE0 8400
86 C/EXEC SQL 8500
87 9 C+ FETCH C1 INTO :PROJNO, :EMPNO, :NAME, :SALARY 8600
88 C/END-EXEC 8700
89 C EXCPTRECB 8800
90 C END 8900
91 C DONE1 TAG 9000
92 C/EXEC SQL 9100
93 10 C+ CLOSE C1 9200
94 C/END-EXEC 9300
95 C* 9400
96 C* For all project ending at a date later than the raise date 9500
97 C* (that is, those projects potentially affected by the salary raises), 9600
98 C* generate a report containing the project number, project name, 9700
99 C* the count of employees participating in the project, and the 9800
100 C* total salary cost of the project. 9900
101 C* 10000
102 C* Write out the header for report 2. 10100
103 C* 10200
104 C EXCPTRECC 10300
105 11 C/EXEC SQL 10400
106 C+ DECLARE C2 CURSOR FOR 10500
107 C+ SELECT EMPPROJACT.PROJNO, PROJNAME, COUNT(*), 10600
108 C+ SUM((DAYS(EMENDATE) - DAYS(EMSTDATE)) * EMPTIME * 10700
109 C+ DECIMAL((SALARY/:WRKDAY),8,2)) 10800
110 C+ FROM CORPDATA/EMPPROJACT, CORPDATA/PROJECT, CORPDATA/EMPLOYEE 10900
111 C+ WHERE EMPPROJACT.PROJNO = PROJECT.PROJNO AND 11000
112 C+ EMPPROJACT.EMPNO = EMPLOYEE.EMPNO AND 11100
113 C+ PRENDATE > :RDATE 11200
114 C+ GROUP BY EMPPROJACT.PROJNO, PROJNAME 11300
115 C+ ORDER BY 1 11400
116 C/END-EXEC 11500
117 C* 11600
118 C/EXEC SQL OPEN C2 11700
119 C/END-EXEC 11800
120 C* 11900
121 C* Fetch and write the rows to QPRINT. 12000
122 C* 12100
123 C/EXEC SQL WHENEVER NOT FOUND GO TO DONE2 12200
124 C/END-EXEC 12300
xxxxST1 VxRxMx yymmdd Create SQL RPG Program RPGEX 08/06/07 12:55:22 Page 4
125 C SQLCOD DOUNE0 12400
126 C/EXEC SQL 12500
127 12 C+ FETCH C2 INTO :RPT2 12600
128 C/END-EXEC 12700
129 C EXCPTRECD 12800
130 C END 12900
131 C DONE2 TAG 13000
132 C/EXEC SQL CLOSE C2 13100
133 C/END-EXEC 13200
134 C RETRN 13300
135 C* 13400
136 C* Error occurred while updating table. Inform user and roll back 13500
137 C* changes. 13600
138 C* 13700
139 C UPDERR TAG 13800
140 C EXCPTRECE 13900
141 13 C/EXEC SQL WHENEVER SQLERROR CONTINUE 14000
142 C/END-EXEC 14100
143 C* 14200
144 14 C/EXEC SQL 14300
145 C+ ROLLBACK 14400
146 C/END-EXEC 14500
147 C RETRN 14600
148 C* 14700
149 C* Error occurred while generating reports. Inform user and exit. 14800
150 C* 14900
151 C RPTERR TAG 15000
152 C EXCPTRECF 15100
153 C* 15200
154 C* All done. 15300
155 C* 15400
156 C FINISH TAG 15500
157 OQPRINT E 0201 RECA 15700
158 O 45 'REPORT OF PROJECTS AFFEC' 15800
159 O 64 'TED BY EMPLOYEE RAISES' 15900
160 O E 01 RECA 16000
161 O 7 'PROJECT' 16100
162 O 17 'EMPLOYEE' 16200
163 O 32 'EMPLOYEE NAME' 16300
164 O 60 'SALARY' 16400
165 O E 01 RECB 16500
166 O PROJNO 6 16600
167 O EMPNO 15 16700
168 O NAME 50 16800
169 O SALARYL 61 16900
170 O E 22 RECC 17000
171 O 42 'ACCUMULATED STATISTIC' 17100
172 O 54 'S BY PROJECT' 17200
173 O E 01 RECC 17300
174 O 7 'PROJECT' 17400
175 O 56 'NUMBER OF' 17500
176 O 67 'TOTAL' 17600
177 O E 02 RECC 17700
178 O 6 'NUMBER' 17800
179 O 21 'PROJECT NAME' 17900
180 O 56 'EMPLOYEES' 18000
181 O 66 'COST' 18100
182 O E 01 RECD 18200
183 O PRJNUM 6 18300
184 O PNAME 45 18400
185 O EMPCNTL 54 18500
186 O PRCOSTL 70 18600
187 O E 01 RECE 18700
188 O 28 '*** ERROR Occurred while' 18800
189 O 52 ' updating table. SQLCODE' 18900
190 O 53 '=' 19000
191 O SQLCODL 62 19100
192 O E 01 RECF 19200
193 O 28 '*** ERROR Occurred while' 19300
194 O 52 ' generating reports. SQL' 19400
195 O 57 'CODE=' 19500
196 O SQLCODL 67 19600
* * * * * E N D O F S O U R C E * * * * *
xxxxST1 VxRxMx yymmdd Create SQL RPG Program RPGEX 08/06/07 12:55:22 Page 5
CROSS REFERENCE
Data Names Define Reference
ACTNO 68 SMALL INTEGER PRECISION(4,0) COLUMN (NOT NULL) IN CORPDATA.EMPPROJACT
BIRTHDATE 48 DATE(10) COLUMN IN CORPDATA.EMPLOYEE
BONUS 48 DECIMAL(9,2) COLUMN IN CORPDATA.EMPLOYEE
COMM **** COLUMN
48 68
COMM 48 DECIMAL(9,2) COLUMN IN CORPDATA.EMPLOYEE
COMMI 31 DECIMAL(7,2)
48 68
CORPDATA **** SCHEMA
48 68 68 105 105 105
C1 68 CURSOR
77 86 92
C2 105 CURSOR
118 126 132
DEPTNO 8 CHARACTER(3) IN RPT1
DEPTNO 105 CHARACTER(3) COLUMN (NOT NULL) IN CORPDATA.PROJECT
DONE1 91 LABEL
83
DONE2 131 LABEL
123
EDLEVEL 48 SMALL INTEGER PRECISION(4,0) COLUMN (NOT NULL) IN CORPDATA.EMPLOYEE
EMENDATE 68 DATE(10) COLUMN IN CORPDATA.EMPPROJACT
EMENDATE **** COLUMN
105
EMPCNT 26 SMALL INTEGER PRECISION(4,0) IN RPT2
EMPLOYEE **** TABLE IN CORPDATA
48 68 105
EMPLOYEE **** TABLE
68 105
EMPNO 17 CHARACTER(6)
86
EMPNO 48 CHARACTER(6) COLUMN (NOT NULL) IN CORPDATA.EMPLOYEE
EMPNO **** COLUMN IN EMPPROJACT
68 68 68 105
EMPNO **** COLUMN IN EMPLOYEE
68 105
EMPNO 68 CHARACTER(6) COLUMN (NOT NULL) IN CORPDATA.EMPPROJACT
EMPPROJACT **** TABLE
68 68 105 105 105 105
EMPPROJACT **** TABLE IN CORPDATA
68 105
EMPTIME 68 DECIMAL(5,2) COLUMN IN CORPDATA.EMPPROJACT
EMPTIME **** COLUMN
105
EMSTDATE 68 DATE(10) COLUMN IN CORPDATA.EMPPROJACT
EMSTDATE **** COLUMN
105
FINISH 156 LABEL
FIRSTNME 48 VARCHAR(12) COLUMN (NOT NULL) IN CORPDATA.EMPLOYEE
FIRSTNME **** COLUMN
68
HIREDATE 48 DATE(10) COLUMN IN CORPDATA.EMPLOYEE
JOB 48 CHARACTER(8) COLUMN IN CORPDATA.EMPLOYEE
LASTNAME 48 VARCHAR(15) COLUMN (NOT NULL) IN CORPDATA.EMPLOYEE
LASTNAME **** COLUMN
68
MAJPRJ 8 CHARACTER(6) IN RPT1
MAJPROJ 105 CHARACTER(6) COLUMN IN CORPDATA.PROJECT
MIDINIT 48 CHARACTER(1) COLUMN (NOT NULL) IN CORPDATA.EMPLOYEE
NAME 18 CHARACTER(30)
86
PERCNT 33 DECIMAL(7,2)
48
PHONENO 48 CHARACTER(4) COLUMN IN CORPDATA.EMPLOYEE
PNAME 25 CHARACTER(36) IN RPT2
PRCOST 27 DECIMAL(9,2) IN RPT2
PREND 8 DATE(10) IN RPT1
PRENDATE **** COLUMN
105
xxxxST1 VxRxMx yymmdd Create SQL RPG Program RPGEX 08/06/07 12:55:22 Page 6
PRENDATE 105 DATE(10) COLUMN IN CORPDATA.PROJECT
PRJNUM 24 CHARACTER(6) IN RPT2
CROSS REFERENCE
PROJECT **** TABLE IN CORPDATA
105
PROJECT **** TABLE
105
PROJNAME **** COLUMN
105 105
PROJNAME 105 VARCHAR(24) COLUMN (NOT NULL) IN CORPDATA.PROJECT
PROJNM 8 VARCHAR(24) IN RPT1
PROJNO 8 CHARACTER(6) IN RPT1
86
PROJNO **** COLUMN
68 68
PROJNO 68 CHARACTER(6) COLUMN (NOT NULL) IN CORPDATA.EMPPROJACT
PROJNO **** COLUMN IN EMPPROJACT
105 105 105
PROJNO **** COLUMN IN PROJECT
105
PROJNO 105 CHARACTER(6) COLUMN (NOT NULL) IN CORPDATA.PROJECT
PRSTAFF 105 DECIMAL(5,2) COLUMN IN CORPDATA.PROJECT
PRSTD 8 DATE(10) IN RPT1
PRSTDATE 105 DATE(10) COLUMN IN CORPDATA.PROJECT
RDATE 32 CHARACTER(10)
105
RESEM 8 CHARACTER(6) IN RPT1
RESPEMP 105 CHARACTER(6) COLUMN (NOT NULL) IN CORPDATA.PROJECT
RPTERR 151 LABEL
59
RPT1 8 STRUCTURE
RPT2 23 STRUCTURE
126
SALARY 19 DECIMAL(9,2)
86
SALARY **** COLUMN
48 48 68 105
SALARY 48 DECIMAL(9,2) COLUMN IN CORPDATA.EMPLOYEE
SEX 48 CHARACTER(1) COLUMN IN CORPDATA.EMPLOYEE
STAFF 8 DECIMAL(5,2) IN RPT1
UPDERR 139 LABEL
45
WORKDEPT 48 CHARACTER(3) COLUMN IN CORPDATA.EMPLOYEE
WRKDAY 30 SMALL INTEGER PRECISION(4,0)
105
No errors found in source
196 Source records processed
* * * * * E N D O F L I S T I N G * * * * *