Retrieving a single row of data into host variables

If you know that your query returns only one row, you can specify one or more host variables to contain the column values of the retrieved row.

About this task

Restriction: These instructions do not apply if you do not know how many rows Db2 will return or if you expect Db2 to return more than one row. In these situations, use a cursor. A cursor enables an application to return a set of rows and fetch either one row at a time or one rowset at a time from the result table.

Procedure

To retrieve a single row of data into host variables:

In the SELECT statement specify the INTO clause with the name of one or more host variables to contain the retrieved values. Specify one variable for each value that is to be retrieved. The retrieved value can be a column value, a value of a host variable, the result of an expression, or the result of an aggregate function.

Recommendation: If you want to ensure that only one row is returned, specify the FETCH FIRST 1 ROW ONLY clause. Consider using the ORDER BY clause to control which row is returned. If you specify both the ORDER BY clause and the FETCH FIRST clause, ordering is performed on the entire result set before the first row is returned.

Db2 assigns the first value in the result row to the first variable in the list, the second value to the second variable, and so on.

If the SELECT statement returns more than one row, Db2 returns an error, and any data that is returned is undefined and unpredictable.

Examples

Example: Retrieving a single row into a host variable

Suppose that you are retrieving the LASTNAME and WORKDEPT column values from the DSN8C10.EMP table for a particular employee. You can define a host variable in your program to hold each column value and then name the host variables in the INTO clause of the SELECT statement, as shown in the following COBOL example.

MOVE '000110' TO CBLEMPNO.  
EXEC SQL
  SELECT LASTNAME, WORKDEPT
    INTO :CBLNAME, :CBLDEPT
    FROM DSN8C10.EMP
    WHERE EMPNO = :CBLEMPNO
END-EXEC.

In this example, the host variable CBLEMPNO is preceded by a colon (:) in the SQL statement, but it is not preceded by a colon in the COBOL MOVE statement.

This example also uses a host variable to specify a value in a search condition. The host variable CBLEMPNO is defined for the employee number, so that you can retrieve the name and the work department of the employee whose number is the same as the value of the host variable, CBLEMPNO; in this case, 000110.

In the DATA DIVISION section of a COBOL program, you must declare the host variables CBLEMPNO, CBLNAME, and CBLDEPT to be compatible with the data types in the columns EMPNO, LASTNAME, and WORKDEPT of the DSN8C10.EMP table.

Example: Ensuring that a query returns only a single row

You can use the FETCH FIRST 1 ROW ONLY clause in a SELECT statement to ensure that only one row is returned. This action prevents undefined and unpredictable data from being returned when you specify the INTO clause of the SELECT statement. The following example SELECT statement ensures that only one row of the DSN8C10.EMP table is returned.

EXEC SQL
  SELECT LASTNAME, WORKDEPT
    INTO :CBLNAME, :CBLDEPT
    FROM DSN8C10.EMP
    FETCH FIRST 1 ROW ONLY
END-EXEC.

You can include an ORDER BY clause in the preceding example to control which row is returned. The following example SELECT statement ensures that the only row returned is the one with a last name that is first alphabetically.

EXEC SQL
   SELECT LASTNAME, WORKDEPT
     INTO :CBLNAME, :CBLDEPT
     FROM DSN8810.EMP
     ORDER BY LASTNAME
     FETCH FIRST 1 ROW ONLY
END-EXEC.

Example: Retrieving the results of host variable values and expressions into host variables

When you specify a list of items in the SELECT clause, that list can include more than the column names of tables and views. You can request a set of column values mixed with host variable values and constants. For example, the following query requests the values of several columns (EMPNO, LASTNAME, and SALARY), the value of a host variable (RAISE), and the value of the sum of a column and a host variable (SALARY and RAISE). For each of these five items in the SELECT list, a host variable is listed in the INTO clause.

MOVE 4476 TO RAISE.
MOVE '000220' TO PERSON.
EXEC SQL
  SELECT EMPNO, LASTNAME, SALARY, :RAISE, SALARY + :RAISE
    INTO :EMP-NUM, :PERSON-NAME, :EMP-SAL, :EMP-RAISE, :EMP-TTL
    FROM DSN8C10.EMP
    WHERE EMPNO = :PERSON
END-EXEC.

The preceding SELECT statement returns the following results. The column headings represent the names of the host variables.

EMP-NUM    PERSON-NAME    EMP-SAL      EMP-RAISE    EMP-TTL
=======    ===========    =======      =========    =======
 000220    LUTZ             29840           4476      34316

Example: Retrieving the result of an aggregate function into a host variable

A query can request summary values to be returned from aggregate functions and store those values in host variables. For example, the following query requests that the result of the AVG function be stored in the AVG-SALARY host variable.

MOVE 'D11' TO DEPTID.
EXEC SQL
  SELECT WORKDEPT, AVG(SALARY)
    INTO :WORK-DEPT, :AVG-SALARY
    FROM DSN8C10.EMP
    WHERE WORKDEPT = :DEPTID
END-EXEC.