SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-11
Using the SELECT Statement in Programs
The INTO clause of the SELECT statement is used to return a single-row result of a
query to a host variable. Here is an example of a single-row SELECT statement that
selects by a primary-key column, col1:
EXEC SQL
SELECT col2,
col3,
col4
INTO :hv2,
:hv3,
:hv4
FROM MYTABLE
WHERE col1 = :hvkey
END-EXEC.
In this example, the WHERE clause specifies that the selected row contains a primary
key, col1, whose value is equal to the value of a specified host variable. Only one
row is retrieved from the table because a unique, primary-key value is used for the
selection.
Here is an example of a single-row SELECT statement that selects by a nonkey
column, price:
EXEC SQL
SELECT partnum,
partdesc,
price,
qty_available
INTO :part-no of parts,
:part-desc of parts,
:price of parts,
:qty_available of parts
FROM MYTABLE
WHERE price = :hvdata
END-EXEC.
The WHERE clause specifies that the column price contains a value equal to the value
of a host variable, :hvdata.
When the SELECT statement is executed, the system scans the database to find the
first row with the specified value in price. When found, this specific row is returned to
the program. Because price is not a primary key, and assuming that UNIQUE was not
specified for price in any alternate index, the system then reads the rest of the table to
make sure the row it found is the only qualifying row; if it is not, the system returns an
error.