How to read an entire whole file and retrieve data from the DB2 database

Products

• Easytrieve Report Generator
• PAN/SQL

Issue/Introduction

This example below reads the entire personnel file and retrieves data from the database based on employee number.

Environment

Easytrieve Report Generator, release 11.6
Pan/SQL 2.4C

Resolution

PARM SSID(xxxx)
FILE PERSNL
P-EMPNO 9 5 A
*
SQL INCLUDE FROM owner.DB2FILE1
SQL DECLARE C1 CURSOR FOR +
        SELECT DEPT, LASTNAME, FIRSTNAME, +
        NETPAY FROM owner.DB2FILE1 +
        WHERE EMPNO = :P-EMPNO
*
JOB INPUT PERSNL
SQL OPEN C1
PERFORM CHECK-SQL-CODE
DO WHILE SQLCODE NE 100
 SQL FETCH C1 INTO :DEPT, :LASTNAME, :FIRSTNAME, :NETPAY
PERFORM CHECK-SQL-CODE
 IF SQLCODE NE 100
    PRINT RPRT1
 END-IF
END-DO
SQL CLOSE C1
 PERFORM CHECK-SQL-CODE
*
CHECK-SQL-CODE. PROC
  IF SQLCODE NE 0 AND SQLCODE NE 100
    DISPLAY 'ERROR - SQLCODE = ' SQLCODE
  STOP EXECUTE
    END-IF
END-PROC
*
REPORT RPRT1 LINESIZE 80
TITLE 'EXAMPLE FOR INPUT FILE'
LINE DEPT, P-EMPNO, LASTNAME, FIRSTNAME, NETPAY