Retrieve single rows

The set of rows that a SELECT statement returns is its active set. A singleton SELECT statement returns a single row. You can use embedded SELECT statements to retrieve single rows from the database into host variables. When a SELECT statement returns more than one row of data, however, a program must use a cursor to retrieve rows one at a time. Multiple-row select operations are discussed in Retrieve multiple rows.

To retrieve a single row of data, simply embed a SELECT statement in your program. The following example shows how you can write the embedded SELECT statement using :

EXEC SQL SELECT avg (total_price)
   INTO :avg_price
   FROM items
   WHERE order_num in
      (SELECT order_num from orders 
      WHERE order_date < date('6/1/98') );

The INTO clause is the only detail that distinguishes this statement from any example in Compose SELECT statements or Compose advanced SELECT statements. This clause specifies the host variables that are to receive the data that is produced.

When the program executes an embedded SELECT statement, the database server performs the query. The example statement selects an aggregate value so that it produces exactly one row of data. The row has only a single column, and its value is deposited in the host variable named avg_price. Subsequent lines of the program can use that variable.

You can use statements of this kind to retrieve single rows of data into host variables. The single row can have as many columns as desired. If a query produces more than one row of data, the database server cannot return any data. It returns an error code instead.

You should list as many host variables in the INTO clause as there are items in the select list. If, by accident, these lists are of different lengths, the database server returns as many values as it can and sets the warning flag in the fourth field of SQLWARN.