SQL/MX Programming Manual for C and COBOL (G06.24+, H06.03+)

Simple and Compound Statements
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
5-2
Single-Row SELECT Statement
Single-Row SELECT Statement
A single-row SELECT statement retrieves a single row of data from one or more tables
or views and places the column values in corresponding host variables. Use this
general syntax:
For complete syntax, see the SELECT statement in the SQL/MX Reference Manual.
The search condition is specified so that one row is selected. For information on
fetching a set of rows one row at a time by using a SELECT statement that specifies a
cursor, see Section 6, Static SQL Cursors.
After a SELECT statement executes, NonStop SQL/MX returns a value to the
SQLSTATE variable. If no rows were found satisfying the search condition, the value of
SQLSTATE is 02000 (no data). For information on checking the value of SQLSTATE,
see Section 13, Exception Handling and Error Conditions.
Using a Primary Key Value to Select Data
You can use a primary key value to select a single row of data.
Example
Use the SELECT statement to return an employee’s first name, last name, and
department 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 hv_this_employee. The SELECT
statement retrieves only one row because the primary key value is unique.
...
hv_this_employee = input_empnum; /* set host variable */
...
EXEC SQL SELECT first_name, last_name, deptnum
INTO :hv_first_name, :hv_last_name, :hv_deptnum
FROM persnl.employee
WHERE empnum = :hv_this_employee;
Example
Use the SELECT statement to return customer information from the CUSTOMER table
by using a primary key value (CUSTNUM column). The WHERE clause specifies that
the selected row contains a primary key with a value equal to the host variable named
FIND-THIS-CUSTOMER. The SELECT statement retrieves only one row because the
primary key value is unique.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 CUSTOMER.
SELECT column [,column]...
INTO :hostvar [,:hostvar]...
FROM table-name
WHERE search-condition
C
COBOL