SELECT Statement (select_statement)

A SELECT statement (select_statement) defines and creates an unnamed results table.

Structure

Syntax Syntax

<select_statement> ::=
  <query_expression>
    [<order_clause>]
    [<limit_clause>]
    [<update_clause>]
    [<lock_option>]
    [FOR REUSE]

<limit_clause> ::=
  LIMIT <row_count>
| LIMIT <offset>,<row_count>

<row_count> ::=
  <unsigned_integer>
| <parameter_name>

<offset> ::=
  <unsigned_integer>
| <parameter_name>

End of the code.

Examples

SQL Tutorial, Data Query

Explanation

The rules specified for the DECLARE CURSOR statement also apply for the SELECT statement (select_statement).

The search strategy used determines how the search is carried out. When the SELECT statement is executed, the system searches for all the rows in the results table and generates the results table physically. When a FETCH statement is executed, the system searches for the next relevant row in the results table but does not store it physically. This must be taken into account for the time behavior of FETCH statements.

Updatable Results Table

A results table and its underlying base tables can be updated if the QUERY statement fulfills the following conditions:

The QUERY statement consists of a DECLARE CURSOR statement.
The QUERY expression (query_expression) may only consist of one QUERY specification (query_spec).
Only one base table or updatable view table may be specified in the FROM clause of the QUERY specification.
The key word DISTINCT or a GROUP or HAVING clause cannot be specified.
Expressions cannot contain a set function (set_function_spec).

ORDER Clause

The ORDER clause (order_clause) specifies a sort sequence for a result table.

LIMIT Clause

You can use the LIMIT clause (limit_clause) to limit the maximum number of rows in the result table. You either enter only the desired maximum number of rows or the additional information from which row the list should begin (offset value). The offset value of the initial row is 0. If no offset value is specified, the rows are listed from the beginning of the result table.

If you use a ROWNO predicate or a TOP syntax element, then you may not use the LIMIT clause.

If both a LIMIT clause and an ORDER clause are entered, then all result rows are sorted and the relevant number of rows is displayed. Normally, the result differs from what you would receive if no ORDER clause was entered.

LOCK Option

The LOCK option (lock_option) determines which locks are to be set on the read rows.

UPDATE Clause

An UPDATE clause (update_clause) can only be specified for updatable result tables. With updatable results tables, any position within a particular results table always corresponds to a position in the underlying tables and thus, ultimately, to a position in one or more base tables.

If an UPDATE clause was specified, the base tables can be updated using the position in the results table (CURRENT OF <result_table_name>) using an UPDATE statement or a DELETE statement. You use a LOCK statement to request a lock for the relevant rows in each of the base tables concerned.

FOR REUSE

If the result table is to be specified in the FROM clause of a subsequent QUERY statement, the table should be specified using the FOR REUSE keywords. If FOR REUSE is not specified, the reusability of the results table depends on internal system strategies.

Since specifying FOR REUSE increases the response times of some QUERY statements, FOR REUSE should only be specified if it is necessary to reuse the results table.

More Information

QUERY Statement