Show TOC

QUERY Statement (query_statement)Locate this document in the navigation structure


A QUERY statement ( query_statement) specifies a result table that can be ordered. There are different ways of formulating a QUERY statement.



SQL Tutorial, Data Query


A QUERY statement generates a named/unnamed result table. In contrast to a named result table, an unnamed result table cannot be specified in a FROM clause or in CURRENT OF <result_table_name> of a subsequent SQL statement.

The following QUERY statements can be distinguished:

Basic types of QUERY statement



A named result table is defined and generated.

SELECT statement ( select_statement)

An unnamed results table is defined and generated.

Recursive DECLARE CURSOR statement

This statement is used to generate bills of material.

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

The order of rows in the results table depends on the system's internal search strategies and does not follow any predictable order. The only reliable way to sort the rows of results is to specify an ORDER clause.

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 must consist of a DECLARE CURSOR statement.

  • The QUERY expression (query_expression) can only consists of one QUERY specification ( query_spec).

  • Only one base table or updatable view table may be specified in the FROM of the QUERY specification.

  • The key word DISTINCT or a GROUP or HAVING clause cannot be specified.

  • Expressions cannot contain a set function.

  • The results table must be a named results table. In other words, it cannot have been generated by a SELECT statement.

More Information