QUERY Statement (query_statement)

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

Syntax

<query_statement> ::= <declare_cursor_statement>
                    | <
recursive_declare_cursor_statement>
                    | <
select_statement>

Examples

SQL Tutorial Data Query

Explanation

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

The following QUERY statements can be distinguished:

Basic types of QUERY statement

DECLARE CURSOR 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 SELECT statement declare_cursor_statement also apply to the DECLARE CURSOR statement declare_cursor_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.

Updateable 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 updateable view table may be specified in the FROM clause of the QUERY specification.

?     The key word DISTINCT or a GROUP or HAVING clause may not 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.

See also:

Result Table Name

Glossary, Table