Show TOC Start of Content Area

Function documentation Single-Row Query  Locate the document in its SAP Library structure

Use

A single-row query is a SELECT statement that produces a result set with at most one row. For example, such statement could be a SELECT statement with a fully specified primary key.

Activities

Executing a Single-Row Query

In SQLJ, a single-row query can be executed and its result set data can be retrieved with a single statement:

Syntax

SELECT ... INTO <select target list> .

The INTO-clause contains a list of host variables or host expressions that receive the result set data. For more information, see SELECT INTO Statement.

Note

Open SQL/SQLJ does not allow to process the result set of a SELECT * in a single-row query. The reason is that Open SQL does not guarantee the order of the columns. It may be different at runtime and at design time.

Example

#sql [ctx] { SELECT first_name, last_name

            INTO :firstName, :lastName

            FROM employee

            WHERE id = '911'};

A single-row query. It is used to retrieve the columns FISRT_NAMEand LAST_NAME of the database table EMPLOYEE into the java variables firstNameand lastName, respectively.

You can also use arithmetic expressions, such as AVG, SUM, MAX, and so on, in a single-row query.

Example

BigDecimal salary;

 

#sql [ctx] {SELECT MAX(salary)

            INTO :salary

            FROM employee};

Arithmetic expression into a single-row query. This query retrieves the highest salary entered for an employee in the EMPLOYEE table. The query returns a single numeric value, which is assigned to the host variable salary.

Error Handling

If a single row query produces an empty result set, an SQLException with SQLState equal to ″02000″ (no data) is thrown. In Open SQL/SQLJ, an exception of the subclass com.sap.sql.NoDataException is thrown.

If the result set contains more than one rows, an SQLException with SQLState equal to ″21000″(cardinality violation) is thrown. In Open SQL/SQLJ, an exception of the subclass com.sap.sql.CardinalityViolationException is thrown.

If you want to make your code portable with third-party SQLJ translators, you should not use Open SQL/SQLJ exception classes.

Example

String firstName, lastName;

 

try {

  #sql [ctx] { SELECT first_name, last_name

             INTO :firstName, :lastName

             FROM employee

             WHERE id = '911'};

} catch (com.sap.sql.NoDataException ex) {

    System.out.println(″No data″);

} catch (com.sap.sql.CardinalityViolationException) {       

    System.out.println(″Too many rows in the result set″);

}

Error handling of single-row query in Open SQL/SQLJ. If a single-row query returns no data, a com.sap.sql.NoDataException will be thrown. If the result set produced by the query contains more than one row, a com.sap.sql.CardinalityViolationException will be thrown.

Example

String firstName, lastName;

 

   try {

     #sql [ctx] { SELECT first_name, last_name

                INTO :firstName, :lastName

                FROM employee

                WHERE id = '911'};

   } catch (SQLException ex) {

      String sqlState = ex.getSQLState();

      if (sqlState.equals(″02000″)) {

         System.out.println(″No data″);

      } else if (sqlState.equals(″21000″)) {

         System.out.println(″Too many rows in the result set″);

      } else {

         throw ex;

      }

   }

Error handling of single-row query in general SQLJ. If an error occurs, a single-row query will throw an SQLException. The SQLState of this exception will have the value ″02000″, if an empty result set is returned by the query. The value ″21000″indicates that the result set contains more than one row.

 

See also:

ResultSet Iterators

 

End of Content Area