Mimer SQL Reference Manual TOC PREV NEXT INDEX

Mimer Developer Site



An identifier is defined as a sequence of one or more characters forming a unique name.

Identifiers are constructed according to certain fixed rules. It is useful to distinguish between SQL identifiers, which are local to SQL statements, and host identifiers, which relate to the host programming language.

Rules for constructing the latter may vary between host languages.

SQL Identifiers

SQL identifiers consist of a sequence of one or more characters. The maximum length of an SQL identifier is 128 characters.

SQL identifiers (except for delimited identifiers) must begin with a letter or one of the special characters $ or #, and may only contain letters, digits and the special characters $, # and _.

The case of letters in identifiers is not significant, unless it is a delimited identifier. All non-delimited letters are treated as uppercase.

Delimited identifiers means identifiers enclosed in quotation marks ("). Such identifiers are special in three aspects:

The following examples illustrate the general rules for forming SQL identifiers:

COLUMN+1 is an expression
14 is an integer literal
TABLE NAME contains a blank
SELECT is a reserved word

Note: Leading blanks are significant in delimited identifiers.

Naming Objects

Objects in the database may be divided into two classes:

Qualified Object Names

Names of private objects in the database may always be qualified by the name of the schema to which they belong. The schema name is separated from the object name by a period, with the general syntax schema.object.

If a qualified object name is specified when an object is created, it will be created in the named schema. If an object name is unqualified, a schema name with the same name as the current ident is assumed.

It is recommended that object names are always qualified with the schema name in embedded SQL statements, to avoid confusion if the same program is run by different Mimer SQL idents.

Names of columns in tables or views are used in SQL statements both as an explicit indication of the column itself and as an indication of the values stored in the column.

When the name of a column is expressed in its unqualified form it is syntactically referred to as a column-name.

When the name of a column must be expressed unambiguously it is generally expressed in its fully qualified form (i.e. schema.table.column or table.column) and this is syntactically referred to as a column-reference.

It is possible for a column-reference to be the unqualified name of a column in contexts where this is sufficient to unambiguously identify the column.

When the name of a column is used to indicate the column itself (e.g. in CREATE TABLE statements), a column-name must be used (i.e. the name of the column cannot be qualified).

The exception to this is in the COMMENT ON COLUMN statement where a column-reference is required because the name of the column must be qualified by the name of the table or view to which it belongs.

The contexts where the name of a column refers to the values stored in the column are:

In these contexts a column-reference must be used to identify the column.

The column name qualifiers which may be used in a particular SQL statement are determined by the way the table is identified in the FROM clause of the SELECT statement.

Alternative names (correlation names) may be introduced in the FROM clause, and the table reference used to qualify column names must conform to the following rules:


but not


but not


Outer References

In some constructions where subselects are used in search conditions, see Select Specification, it may be necessary to refer in the lower level subselect to a value in the current row of a table addressed at the higher level.

A reference to a column of a table identified at a higher level is called an outer reference. The following example shows the outer reference in bold type:

The lower-level subselect is evaluated for every row in the higher level result table. The example selects the name of every hotel with at least one entry in the BOOK_GUEST table.

A qualified column name is an outer reference if, and only if, the following conditions are met:

Host Identifiers

Host identifiers are used in SQL statements to identify objects associated with the host language (variables, declared areas, program statement labels).

Host identifiers are formed in accordance with the rules for forming variable names in the particular host language, see the Mimer SQL Programmer's Manual.

Host identifiers are never enclosed in delimiters and may coincide with SQL reserved words. The length of host identifiers used in SQL statements may not exceed 128 characters (even if the host language accepts longer names).

Whenever the term "host-variable" appears in the syntax diagrams, one of the three following constructions must be used:



:host-identifier1 :host-identifier2


:host-identifier1 INDICATOR :host-identifier2

Host-identifier1 is the name of the main host variable. Host-identifier2 is the name of the indicator variable, used to signal the assignment of a NULL value to the host variable. See the Mimer SQL Programmer's Manual for a description of the use of indicator variables.

The colon preceding the host identifier serves to identify the variable to the SQL compiler and is not part of the variable name in the host language.

Target Variables

A target variable is an item that may be specified as the object receiving the result of an assignment or a SELECT INTO. The objects that may be specified where a target variable is expected differ depending on whether the context is Procedural usage or Embedded usage.

In the syntax diagrams the term target-variable should be replaced by the following construction:

where routine-variable is:

See DECLARE VARIABLE for a description of how a declared-variable is defined, and CREATE FUNCTION or CREATE PROCEDURE for a description of how a routine-parameter is defined.

Note: A routine-variable may only be specified in a Procedural usage context and a host-variable may only be specified in Embedded usage.

Reserved Words

Reserved Words gives a list of keywords reserved in SQL statements. These words must be enclosed in quotation marks (") if they are used as SQL identifiers.



Standard Compliance

This section summarizes standard compliance concerning identifiers.

The use of the special characters $ and # in identifiers is a Mimer SQL extension.

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