Show TOC

Background documentationQUERY 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.

Structure

Syntax Syntax

  1. <query_statement> ::=
  <declare_cursor_statement>
| <recursive_declare_cursor_statement>
| <select_statement>
End of the code.
Examples

SQL Tutorial, Data Query

Explanation

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:

QUERY Statement (query_statement)

Basic types of QUERY statement

Explanation

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 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

Result Table Name

Glossary, Table

Data Query