SQL Programming Manual for TAL

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for TAL—527887-001
3-32
SELECT
•
A single-row SELECT returns a single row or value.
•
A multi-row SELECT returns multiple rows one row at a time.
To execute a SELECT statement, a TAL program's process accessor ID (PAID) must
have read access to all protection views, tables, and underlying tables of all shorthand
views referred to in the statement.
In general, specifying a select operation programmatically in a TAL program is the
same as specifying an select operation using SQLCI commands. With SQLCI, you
specify the desired values in an SELECT statement, while in a program, you set one or
more host variables to the desired values and then use the host variables in the
SELECT statement.
Executing a Single-Row SELECT Statement
A single-row select statement returns a single row to the program. The INTO clause
returns the single-row to one or more host variables. The row is identified in a WHERE
clause by a unique key value or a unique value of a column in the row.
Using a Value of a Column. This example shows a SELECT statement that returns
only one row based on unique value of a column in the row (a non-key value). This
example retrieves a customer’s name and address. Each customer is identified by a
unique number so that only one customer can satisfy this query. This example:
•
Assumes that a user enters a customer number to retrieve the customer’s name
and address.
•
Declares the host variable :FIND^THIS^CUSTOMER to hold the value of the
customer number whose address is requested (set to 5635 in the example, but
would be entered by a user in an actual program).
•
Declares other host variables for the returned column values.
•
Uses a WHERE clause to specify that the column CUSTOMER.CUSTNUM
contains a unique value that is equal to the value of a host variable
:FIND^THIS^CUSTOMER.
•
Uses an INTO clause to return the single-row to the host variables.
•
Uses BROWSE ACCESS because it does not modify the data, does not want to
wait for locked data, and does not care if it reads uncommitted data.
•
Uses the NOT FOUND condition of the WHENEVER directive to direct control to a
recovery routine (not shown here) if the customer number is not found.
When the SELECT statement is executed, the system scans the database to find the
first row with the specified value in CUSTOMER.CUSTNUM. When found, this specific
row is returned to the program. Because CUSTOMER.CUSTNUM is not a primary key,
the program then reads the rest of the table to make sure the row it found is the only
qualifying row; if it is not, the program returns an error.