SQL/MP Programming Manual for COBOL

Data Retrieval and Modification
HP NonStop SQL/MP Programming Manual for COBOL—529758-003
4-5
Using a Primary Key Value to Select Data
SQL/MP scans the database to find the first row indicated by CUSTNUM and then
returns this row to the program. Because CUSTNUM is not a primary key, SQL/MP
also reads the remainder of the table to verify that the row returned is the only
qualifying row. If it is not, SQL/MP returns an error.
Using a Primary Key Value to Select Data
This SELECT statement returns an employee’s first name, last name, and department
number from the EMPLOYEE table by using a primary key value (EMPNUM column).
The WHERE clause specifies that the selected row contains a primary key with a value
equal to the host variable named FIND-THIS-EMPLOYEE. The SELECT statement
retrieves only one row because the primary key value is unique.
MOVE INPUT-EMPNAME TO FIND-THIS-EMPLOYEE.
EXEC SQL SELECT EMPLOYEE.FIRST-NAME,
EMPLOYEE.LAST-NAME,
Example 4-1. Using a Column Value to Select Data
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 CUSTOMER.
02 CUSTNUM PIC 9(4) DISPLAY.
02 CUSTNAME PIC X(18).
02 STREET PIC X(22).
02 CITY PIC X(14).
02 STATE PIC X(12).
02 POSTCODE PIC X(10).
01 FIND-THIS-CUSTOMER PIC 9(4) VALUE 0.
EXEC SQL END DECLARE SECTION END-EXEC.
PROCEDURE DIVISION.
100-BEGIN.
EXEC SQL WHENEVER NOT FOUND PERFORM :400-NOT-FOUND END-EXEC.
MOVE 5635 TO FIND-THIS-CUSTOMER.
EXEC SQL
SELECT CUSTNAME, STREET, CITY, STATE, POSTCODE
INTO :CUSTNAME, :STREET, :CITY, :STATE, :POSTCODE
FROM SALES.CUSTOMER
WHERE CUSTNUM = :FIND-THIS-CUSTOMER
BROWSE ACCESS
END-EXEC.
DISPLAY CUSTNAME, STREET, CITY, STATE, POSTCODE.
400-NOT-FOUND.
PERFORM 5000-CLOSE-CURSOR.
DISPLAY "CUSTOMER NOT FOUND: "FIND-THIS-CUSTOMER"
PERFORM 8880-ABORT-TRANSACTION.