SQL/MP Query Guide

Retrieving Data: How to Write Queries

HP NonStop SQL/MP Query Guide—524488-003

1-10

Using the SELECT Statement in Programs

A SELECT statement in a program typically retrieves data into a host variable. You can

use two types of SELECT statements in a program:

•

A single-row SELECT (also called a singleton or standalone SELECT) that returns

a single row or value.

•

A multiple-row SELECT (also called a cursor SELECT) that returns multiple rows

one row at a time. Use of a cursor handles the uncertainty involved in retrieving a

variable number of rows.

These paragraphs describe each type of statement; for performance information, see

Section 3, Improving Query Performance Through Query Design.

Single-Row SELECT

A single-row SELECT statement is a request to return a single row to the host

program. This method is preferable to a cursor SELECT when only one row needs to

be retrieved.

The single row is typically one of these:

•

An aggregate without a GROUP BY clause

•

A row identified by a unique key value

•

A row identified by a unique value of a column within a row

This example shows an aggregate without a GROUP BY clause:

SELECT SUM (SALARY) FROM EMPLOYEE ;

For more information about aggregate functions, see Aggregate Functions in

Predicates on page 1-41.

You can write a single SELECT statement to return the desired row, whether the

identifying value is a key value or a nonkey value. Such a SELECT statement contains

a WHERE clause that should uniquely identify one row.

You can also use a unique alternate-key value; to optimize efficiency in such a case,

the index should be defined with the UNIQUE attribute.