H
F* File declaration for QPRINT
F*
FQPRINT O F 132 PRINTER
I*
I* Structure for report 1.
I*
IRPT1 E DSPROJECT
I PROJNAME PROJNM
I RESPEMP RESEM
I PRSTAFF STAFF
I PRSTDATE PRSTD
I PRENDATE PREND
I MAJPROJ MAJPRJ
I*
I DS
I 1 6 EMPNO
I 7 36 NAME
I P 37 412SALARY
I*
I* Structure for report 2.
I*
IRPT2 DS
I 1 6 PRJNUM
I 7 42 PNAME
I B 43 440EMPCNT
I P 45 492PRCOST
I*
I DS
I B 1 20WRKDAY
I P 3 62COMMI
I 7 16 RDATE
I P 17 202PERCNT
C*
C Z-ADD253 WRKDAY
C Z-ADD2000.00 COMMI
C Z-ADD1.04 PERCNT
C MOVEL'1982-06-'RDATE
C MOVE '01' RDATE
C SETON LR
C*
C* Update the selected projects by the new percentage. If an
C* error occurs during the update, ROLLBACK the changes.
C*
C/EXEC SQL WHENEVER SQLERROR GOTO UPDERR
C/END-EXEC
C*
C/EXEC SQL
C+ UPDATE CORPDATA/EMPLOYEE
C+ SET SALARY = SALARY * :PERCNT
C+ WHERE COMM >= :COMMI
C/END-EXEC
C*
C* Commit changes.
C*
C/EXEC SQL COMMIT
C/END-EXEC
C*
C/EXEC SQL WHENEVER SQLERROR GO TO RPTERR
C/END-EXEC
C*
C* Report the updated statistics for each employee assigned to
C* selected projects.
C*
C* Write out the header for report 1.
C*
C EXCPTRECA
C/EXEC SQL DECLARE C1 CURSOR FOR
C+ SELECT DISTINCT PROJNO, EMP_ACT.EMPNO,
C+ LASTNAME||', '||FIRSTNME, SALARY
C+ FROM CORPDATA/EMP_ACT, CORPDATA/EMPLOYEE
C+ WHERE EMP_ACT.EMPNO = EMPLOYEE.EMPNO AND
C+ COMM >= :COMMI
C+ ORDER BY PROJNO, EMPNO
C/END-EXEC
C*
C/EXEC SQL
C+ OPEN C1
C/END-EXEC
C*
C* Fetch and write the rows to QPRINT.
C*
C/EXEC SQL WHENEVER NOT FOUND GO TO DONE1
C/END-EXEC
C SQLCOD DOUNE0
C/EXEC SQL
C+ FETCH C1 INTO :PROJNO,:EMPNO,:NAME, :SALARY
C/END-EXEC
C EXCPTRECB
C END
C DONE1 TAG
C/EXEC SQL
C+ CLOSE C1
C/END-EXEC
C*
C* For all project ending at a date later than the raise date
C* (i.e. those projects potentially affected by the salary raises)
C* generate a report containing the project number, project name,
C* the count of employees participating in the project and the
C* total salary cost of the project.
C*
C* Write out the header for report 2.
C*
C EXCPTRECC
C/EXEC SQL
C+ DECLARE C2 CURSOR FOR
C+ SELECT EMP_ACT.PROJNO, PROJNAME, COUNT(*),
C+ SUM((DAYS(EMENDATE) - DAYS(EMSTDATE)) * EMPTIME *
C+ DECIMAL((SALARY/:WRKDAY),8,2))
C+ FROM CORPDATA/EMP_ACT, CORPDATA/PROJECT, CORPDATA/EMPLOYEE
C+ WHERE EMP_ACT.PROJNO = PROJECT.PROJNO AND
C+ EMP_ACT.EMPNO = EMPLOYEE.EMPNO AND
C+ PRENDATE > :RDATE
C+ GROUP BY EMP_ACT.PROJNO, PROJNAME
C+ ORDER BY 1
C/END-EXEC
C*
C/EXEC SQL OPEN C2
C/END-EXEC
C*
C* Fetch and write the rows to QPRINT.
C*
C/EXEC SQL WHENEVER NOT FOUND GO TO DONE2
C/END-EXEC
C SQLCOD DOUNE0
C/EXEC SQL
C+ FETCH C2 INTO :RPT2
C/END-EXEC
C EXCPTRECD
C END
C DONE2 TAG
C/EXEC SQL CLOSE C2
C/END-EXEC
C RETRN
C*
C* Error occurred while updating table. Inform user and rollback
C* changes.
C*
C UPDERR TAG
C EXCPTRECE
C/EXEC SQL WHENEVER SQLERROR CONTINUE
C/END-EXEC
C*
C/EXEC SQL
C+ ROLLBACK
C/END-EXEC
C RETRN
C*
C* Error occurred while generating reports. Inform user and exit.
C*
C RPTERR TAG
C EXCPTRECF
C*
C* All done.
C*
C FINISH TAG
OQPRINT E 0201 RECA
O 45 'REPORT OF PROJECTS AFFEC'
O 64 'TED BY EMPLOYEE RAISES'
O E 01 RECA
O 7 'PROJECT'
O 17 'EMPLOYEE'
O 32 'EMPLOYEE NAME'
O 60 'SALARY'
O E 01 RECB
O PROJNO 6
O EMPNO 15
O NAME 50
O SALARYL 61
O E 22 RECC
O 42 'ACCUMULATED STATISTIC'
O 54 'S BY PROJECT'
O E 01 RECC
O 7 'PROJECT'
O 56 'NUMBER OF'
O 67 'TOTAL'
O E 02 RECC
O 6 'NUMBER'
O 21 'PROJECT NAME'
O 56 'EMPLOYEES'
O 66 'COST'
O E 01 RECD
O PRJNUM 6
O PNAME 45
O EMPCNTL 54
O PRCOSTL 70
O E 01 RECE
O 28 '*** ERROR Occurred while'
O 52 ' updating table. SQLCODE'
O 53 '='
O SQLCODL 62
O E 01 RECF
O 28 '*** ERROR Occurred while'
O 52 ' generating reports. SQL'
O 57 'CODE='
O SQLCODL 67
IBM DB2 for i: Code example
RPG language programming example