* ------------------------------------------ Procedure definitions
D custcdt         PR

 * ------------------------------------------ Main line procedure
D custcdt         PI

D amt_due         S              9  2
D credit_limit    S              9  2
D customer        S              6  0
D name            S              8
D Nbr_of_cust     S              7  0

 // Variables for checking SQL return code.
D FetchRC         S                   LIKE( SQLcod )
D UpdateRC        S                   LIKE( SQLcod )

D CUSTds        E DS                  EXTNAME(QCUSTCDT) QUALIFIED

D CUSTarr       E DS                  EXTNAME(QCUSTCDT) DIM(5) QUALIFIED

 /free

  // Insert records into another file for processing.

  Exec SQL DROP TABLE QGPL/CUSTCDTtmp;

  // Create a temporary table based on the production table.

  Exec SQL
    CREATE TABLE QGPL/CUSTCDTtmp LIKE QIWS/QCUSTCDT;

  // Insert a set of records.
  Credit_limit = 500;

  Exec SQL
    INSERT INTO QGPL/CUSTCDTtmp
      SELECT * FROM QIWS/QCUSTCDT
      WHERE CdtLmt > :Credit_Limit;

  If SQLcod <> 0; // Something went wrong!
    // . error handling
  Else;
    Nbr_of_cust = SQLerrd(3); //
  Endif;

   // Get one row from the file into a data structure

  Exec SQL
      SELECT * INTO :CUSTds
      FROM QGPL/CUSTCDTtmp
      WHERE cusnum = 397267;

  If SQLcod = 100; // No records selected!
     // Handle no record found.
  Endif;

  // Get specific columns from one row into RPG fields

  Exec SQL
      SELECT Cusnum, Lstnam, Baldue INTO :customer, :name, :amt_due
      FROM QGPL/CUSTCDTtmp
      WHERE Cusnum = 938472;

  // Get the top five customers with the highest BalDue.

  Exec SQL DECLARE TopFiveCsr CURSOR FOR
    SELECT * FROM QGPL/CUSTCDTtmp
     ORDER BY BalDue DESC;

  Exec SQL OPEN TopFiveCsr;

  Exec SQL FETCH TopFiveCsr FOR 5 ROWS INTO :CUSTarr;

  Select;
  When SQLcod <> 0; //
    // Error handling;
  When SQLerrd(3) <> 5; //
    // There weren't five rows to fetch!
  Endsl;

  Exec SQL CLOSE TopFiveCsr;

  // Read through each row with SQL fetch and update current record.
 

  Exec SQL DECLARE ReadCsr CURSOR FOR

    SELECT * FROM QGPL/CUSTCDTtmp;

  Exec SQL OPEN ReadCsr;

  FetchRC = 0;

  DoW FetchRC = 0;

    Exec SQL FETCH ReadCsr INTO :CUSTds;

    FetchRC = SQLcod;

    Select;
    When FetchRC = 100; // No more entries.
      Leave;
    When FetchRC <> 0;  // Other error handling;
      // Set FetchRC to 0 if this is a non-critical
      // error and you want to continue with the
      // next fetch operation.
    Endsl;

    // Update current record.

    Exec SQL
     UPDATE QGPL/CUSTCDTtmp
     SET BalDue = 0
     WHERE CURRENT OF ReadCsr;
    UpdateRC = SQLcod;
    If UpdateRC <> 0; // Error handling.
      // You have to decide if you want to continue
      // and if so, set FetchRC.
      FetchRC = 0;
    Endif;

  Enddo;

  Exec SQL CLOSE ReadCsr;

  *inLR= *on;
 /end-free