Mimer SQL Reference Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer


SELECT


Retrieves data from the tables in the database.

where query-expression is:

Usage

Embedded/Interactive/ODBC/Procedural/JDBC.

In Embedded SQL, the SELECT statement may only be used to declare a cursor or as input to a PREPARE statement.

In a Procedural usage context, the SELECT statement may only be used to declare a cursor.

In Interactive SQL, the SELECT statement is used for interactive data retrieval. See the Mimer SQL User's Manual for more details.

Description

The ORDER BY Clause

The final result table may be ordered according to an ORDER BY clause. Columns in the ORDER BY clause may be identified by a column label created with SELECT AS, or by a column name if this is unambiguous, within the final result table. The names in the first select specification are used in UNION constructions.

For each column in the ORDER BY clause, the sort order may be specified as ASC (ascending) - the default, or DESC (descending). If more than one column is specified, the result table is ordered first by values in the first specified column, then by values in the second, and so on.

The collating sequence for sorting purposes follows the ISO 8859-1 standard character set, shown in Character Set.

The FOR UPDATE OF Clause

If the SELECT statement defines a cursor for UPDATE CURRENT statements, a FOR UPDATE OF clause may be optionally used to list the columns to be updated. If the FOR UPDATE OF clause is used, it must include all the columns to be updated.

Each column specified in the FOR UPDATE CLAUSE must belong to the table or view named in the FROM clause of the SELECT statement, although the columns in FOR UPDATE OF do not need to be specified in the SELECT clause. No column may be named more than once in the FOR UPDATE OF clause.

Column names in the FOR UPDATE OF clause may not be qualified by the name of the table or view. They are implicitly qualified by the table reference in the FROM clause of the select specification.

FOR UPDATE OF may not be specified if the statement defines a read-only result set, see Updatable Result Sets.

The UNION Operator

If several SELECT statements are connected by UNION or UNION DISTINCT, the result is derived by first merging all result tables specified by the separate SELECT statements, and then eliminating duplicate rows from the merged set. All columns in the result table are significant for the purpose of eliminating duplicates.

The UNION ALL operator on the other hand retains all duplicates. The operator can be viewed as a way to concatenate several queries.

The rules described below apply to both UNION and UNION ALL.

All separate result tables from SELECT statements connected by UNION must have the same number of columns and the data types of columns to be merged must be compatible.

The columns in the result table are named in accordance with the columns in the first SELECT statement of the UNION construction.

Separate SELECT statements may be enclosed in parentheses if desired. This does not affect the result of a UNION operation.

See Result Data Types for a description of how the data type of the UNION result is determined.

Restrictions

SELECT access is required on all tables and views specified in a FROM clause.

Notes

If the SELECT statement is used without the ORDER BY clause, the sort order is undefined. This means that the sort order may change if new indexes are created, indexes are dropped, new statistics are gathered or if a new version of the SQL optimizer is installed.

Standard Compliance

Standard
Compliance
Comments
X/Open-95
SQL92
YES
Fully compliant.



Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL Reference Manual TOC PREV NEXT INDEX