SELECT statements that return at most one row

A single row query retrieves at most one row from the database. A single-row query SELECT statement has an INTO clause following the select list and before the FROM clause. The INTO clause contains a list of host variables to receive the value for each select list item. There must be the same number of host variables as there are select list items. The host variables may be accompanied by indicator variables to indicate NULL results.

When the SELECT statement is executed, the database server retrieves the results and places them in the host variables. If the query results contain more than one row, the database server returns an error.

If the query results in no rows being selected, a Row Not Found warning is returned. Errors and warnings are returned in the SQLCA structure. See The SQL Communication Area (SQLCA).

Example

The following code fragment returns 1 if a row from the Employees table is fetched successfully, 0 if the row doesn't exist, and -1 if an error occurs.

EXEC SQL BEGIN DECLARE SECTION;
long   ID;
char   name[41];
char   Sex;
char   birthdate[15];
short int ind_birthdate;
EXEC SQL END DECLARE SECTION;
...
int find_employee( long Employees )
{
  ID = Employees;
  EXEC SQL SELECT GivenName ||
    ' ' || Surname, Sex, BirthDate
    INTO :name, :Sex,
      :birthdate:ind_birthdate
    FROM Employees
    WHERE EmployeeID = :ID;
  if( SQLCODE == SQLE_NOTFOUND ) 
  {
    return( 0 ); /* Employees not found */
  } 
  else if( SQLCODE < 0 ) 
  {
    return( -1 ); /* error */
  } 
  else 
  {
    return( 1 ); /* found */
  }
}