## Data Types in SQL Statements

Mimer SQL supports the following data type categories:

- Character strings, see Character Strings
- National character strings, see National Character Strings
- Binary, see Binary
- Numerical, see Numerical
- Datetime, see Datetime
- Interval, see Interval
- Boolean, see Boolean
- Spatial, see Spatial Data Types.
In SQL statements, you make explicit data type references when creating tables and domains and altering tables. You also use data types in

`CAST`

and stored procedure variable declarations.In addition, there is also a

`ROW`

type that can be used in stored procedures only, for more information see ROW Data Type.## Character Strings

The character string data type stores sequences of bytes that represent alphanumeric data.

The character string data type category contains the following data types:

## Data Type

## Abbreviations

## Description

## Range

`CHARACTER(n)`

`CHAR(n)`

Character string, fixed length n. 1 <= n <= 15 000`CHARACTER VARYING(n)`

`CHAR VARYING(n)`

`VARCHAR(n)`

Variable length character string, maximum length n. 1 <= n <= 15 000`CHARACTER LARGE OBJECT(n[K|M|G])`

`CHAR LARGE OBJECT(n[K|M|G])`

`CLOB(n[K|M|G])`

Variable length character string measured in characters. For information on the object length, see Specifying the CLOB Length.## CHARACTER or CHAR

The

`CHARACTER`

(`CHAR`

) data type stores string values of fixed length in a column.You specify the length of the

`CHAR`

data type as the length of the column when you create a table. You can specify the length to be any value between 1 and 15 000.When Mimer SQL stores values in a column defined as

`CHAR`

, it right-pads the values with spaces to conform with the specified column length.## Note: If you define a data type as CHARACTER or CHAR, that is, without specifying a length, the length of the data type is 1.

## CHARACTER VARYING or CHAR VARYING or VARCHAR

The

`CHARACTER VARYING`

, abbreviated`CHAR VARYING`

or`VARCHAR`

, data type stores strings of varying length.You specify the maximum length of the

`VARCHAR`

data type as the length of the column when you create a table. You can specify the length to be between 1 and 15 000.## CHARACTER LARGE OBJECT or CLOB

The

`CHARACTER LARGE OBJECT`

(`CLOB`

) data type stores character string values of varying length up to the maximum specified as the large object length (`n[K|M|G]`

).The large object length is

`n`

, optionally multiplied by`K|M|G`

.You can specify the maximum length of the

`CLOB`

data type as the length of the column when you create the table.## Specifying the CLOB Length

If you specify

`<n>K`

(kilo), the length (in characters) is`<n>`

multiplied by 1 024.If you specify

`<n>M`

(mega), the length is`<n>`

multiplied by 1 048 576.If you specify

`<n>G`

(giga), the length is`<n>`

multiplied by 1 073 741 824.If you do not specify large object length, Mimer SQL assumes that the length of the data type is 1M.

## Maximum CLOB Length

The maximum length of a

`CLOB`

is determined by the amount of disk space available for its storage.## Using CLOBs

You can work with

`CLOBs`

as follows:

- Retrieving
`CLOBs`

with simple column references in the`SELECT`

clause of a`SELECT`

statement- Assigning
`CLOBs`

using`INSERT`

statements with a`VALUES`

clause- Assigning
`CLOBs`

using`UPDATE`

statements- Adding
`CLOB`

columns using`CREATE TABLE`

or`ALTER TABLE`

- Dropping
`CLOB`

columns using`ALTER TABLE`

- Altering
`CLOB`

column data types using`ALTER TABLE`

There are some restrictions associated with using

`CLOBs`

. The only comparisons supported for`CLOB`

values are using the`NULL`

predicate and using the`LIKE`

predicate.The only scalar functions which can be used on

`CLOB`

columns are`SUBSTRING`

,`CHAR_LENGTH`

and`OCTET_LENGTH`

.A

`CLOB`

column may not be part of any primary key constraint, index, or unique constraint.The comparison restrictions also prevent

`CLOB`

columns from being used in`DISTINCT`

,`GROUP BY`

and`ORDER BY`

clauses, and`UNION`

,`INTERSECT`

and`EXCEPT`

operations.When defining a stored procedure or trigger it is not allowed to use a

`CLOB`

type for a parameter or a variable. It is allowed to create triggers for tables with`CLOB`

columns with one exception, in an instead of trigger it is not possible to reference`CLOB`

columns in the new table.## Collations

All character strings have a collation attribute. A collation determines the order for ordering and comparisons, see the Mimer SQL User's Manual, Collations, for a detailed description of collations.

## National Character Strings

Mimer SQL implements Unicode using the data type

`NCHAR`

(i.e.`NATIONAL CHARACTER`

data type). The`NCHAR`

data type is logically`UTF-32`

, however, it is stored in a compressed form. Application host variables may use any of the three encoding forms`UTF-8`

,`UTF-16`

, or`UTF-32`

when storing`NCHAR`

data in the database. The encoding forms are fully transparent; you may e.g. use`UTF-16`

to store data, and you can use`UTF-8`

for fetching data.The

`CHAR`

data type is based on ISO 8859-1 (Latin1), which is a true subset of Unicode, and therefore`CHAR`

and`NCHAR`

are fully compatible.## Normalization

A Unicode character can have several equivalent representations. There are precomposed characters and there are combining characters that can be used together with base characters to form a specific character. Consider the letter

`E`

with circumflex and dot below, a letter that occurs in Vietnamese. This letter has five possible representations in Unicode:

`U+0045 LATIN CAPITAL LETTER E`

U+0302 COMBINING CIRCUMFLEX ACCENT

U+0323 COMBINING DOT BELOW`U+0045 LATIN CAPITAL LETTER E`

U+0323 COMBINING DOT BELOW

U+0302 COMBINING CIRCUMFLEX ACCENT`U+00CA LATIN CAPITAL LETTER E WITH CIRCUMFLEX`

U+0323 COMBINING DOT BELOW`U+1EB8 LATIN CAPITAL LETTER E WITH DOT BELOW`

U+0302 COMBINING CIRCUMFLEX ACCENT`U+1EC6 LATIN CAPITAL LETTER E WITH CIRCUMFLEX AND DOT BELOW`

Any two of these sequences should compare equal. The Normalization Form C (NFC) of all five sequences is U+1EC6.

In Mimer SQL, Unicode data (

`NCHAR`

) is automatically transformed to NFC. When needed, literals and variables are implicitly normalized. The result of a concatenation will always be normalized, and string functions, like`UPPER`

and`LOWER`

, will always return a normalized result string. This will assert that all Unicode data will be in NFC, thus giving the expected result in search operations.## Example

SQL>create table t(c nchar(1)); SQL>insert into t values(u&'E\0302\0323'); SQL>insert into t values(u&'E\0323\0302'); SQL>insert into t values(u&'\00CA\0323'); SQL>insert into t values(u&'\1EB8\0302'); SQL>insert into t values(u&'\1EC6'); SQL>select count(c) as equal from t where c = u&'\1EC6'; EQUAL ===== 5The normalization forms are fully described in the Unicode standard annex #15, Unicode Normalization Forms (http://www.unicode.org/reports/tr15).

## Case Folding

When converting between upper and lower case most Unicode characters follow a one-to-one case mapping. However, a few characters expand to two or three characters in folding operations.

Folding operations do not always preserve normalization form. In a few instances, the casing operators must normalize after performing their core function.

Consider the following NFC string:

`U+01F0 LATIN SMALL LETTER J WITH CARON,`

`U+0323 COMBINING DOT BELOW`

`U+004A LATIN CAPITAL LETTER J,`

`U+030C COMBINING CARON,`

`U+0323 COMBINING DOT BELOW`

However, the upper case normalized form (NFC) is:

`U+004A LATIN CAPITAL LETTER J,`

`U+0323 COMBINING DOT BELOW,`

`U+030C COMBINING CARON`

The Unicode definitions for one-to-one mappings are found here http://www.unicode.org/Public/6.1.0/ucd/UnicodeData.txt, and the expanding definitions are found here http://www.unicode.org/Public/6.1.0/ucd/SpecialCasing.txt.

## National Character Data Types

The national character string data type category contains the following data types:

## Data Type

## Abbreviations

## Description

## Range

`NATIONAL CHARACTER(n)`

`NATIONAL CHAR(n)`

`NCHAR(n)`

National character string, fixed length n. 1 <= n <= 5 000`NATIONAL CHARACTER VARYING(n)`

`NATIONAL CHAR VARYING(n)`

`NCHAR VARYING(n)`

`NVARCHAR(n)`

Variable length, national character string, maximum length n. 1 <= n <= 5 000`NATIONAL CHARACTER LARGE OBJECT(n[K|M|G])`

`NATIONAL CHAR LARGE OBJECT(n[K|M|G])`

`NCHAR LARGE OBJECT(n[K|M|G])`

`NCLOB(n[K|M|G])`

Variable length national character string measured in characters. For information on the object length, see Specifying the NCLOB Length.## NATIONAL CHARACTER or NATIONAL CHAR or NCHAR

The

`NATIONAL CHARACTER`

(`NCHAR`

) data type stores string values of fixed length in a column. You specify the length of the`NATIONAL CHARACTER`

data type as the length of the column when you create a table. You can specify the length to be any value between 1 and 5 000.When Mimer SQL stores values in a column defined as

`NATIONAL CHARACTER`

, it right-pads the values with spaces to conform with the specified column length.## Note: If you define a data type as NATIONAL CHARACTER or NCHAR, that is, without specifying a length, the length of the data type is 1.

## NATIONAL CHARACTER VARYING or NATIONAL CHAR VARYING or NCHAR VARYING or NVARCHAR

The

`NATIONAL CHARACTER VARYING`

, abbreviated`NVARCHAR`

,`NATIONAL CHAR VARYING`

or`NCHAR VARYING`

, data type stores strings of varying length.You specify the maximum length of the

`NATIONAL CHARACTER VARYING`

data type as the length of the column when you create a table. You can specify the length to be between 1 and 5 000.## NATIONAL CHARACTER LARGE OBJECT or NCLOB

The

`NATIONAL CHARACTER LARGE OBJECT`

(`NCLOB`

) data type stores national character string values of varying length up to the maximum specified as the large object length (`n[K|M|G`

]).The large object length is n, optionally multiplied by

`K|M|G`

.You can specify the maximum length of the

`NCLOB`

data type as the length of the column when you create the table.## Specifying the NCLOB Length

If you specify

`<n>K`

(kilo), the length (in characters) is`<n>`

multiplied by 1 024.If you specify

`<n>M`

(mega), the length is`<n>`

multiplied by 1 048 576.If you specify

`<n>G`

(giga), the length is`<n>`

multiplied by 1 073 741 824.If you do not specify large object length, Mimer SQL assumes that the length of the data type is 1M.

## Maximum NCLOB Length

The maximum length of an

`NCLOB`

is determined by the amount of disk space available for its storage.## Using NCLOBs

You can work with

`NCLOB`

s as follows:

- Retrieving
`NCLOB`

s with simple column references in the`SELECT`

clause of a`SELECT`

statement- Assigning
`NCLOB`

s using`INSERT`

statements with a`VALUES`

clause- Assigning
`NCLOB`

s using`UPDATE`

statements- Adding
`NCLOB`

columns using`CREATE TABLE`

or`ALTER TABLE`

- Dropping
`NCLOB`

columns using`ALTER TABLE`

- Altering
`NCLOB`

column data types using`ALTER TABLE`

There are some restrictions associated with using

`NCLOB`

's. The only comparison supported for`NCLOB`

values are using the`NULL`

predicate and using the`LIKE`

predicate.The only scalar functions which can be used on

`NCLOB`

columns are`SUBSTRING`

,`CHAR_LENGTH`

and`OCTET_LENGTH`

.An

`NCLOB`

column may not be part of any primary key constraint, index, or unique constraint.The comparison restrictions also prevent

`NCLOB`

columns from being used in`DISTINCT`

,`GROUP BY`

and`ORDER BY`

clauses, and`UNION`

,`EXCEPT`

and`INTERSECT`

operations.When defining a stored procedure or trigger it is not allowed to use a

`NCLOB`

type for a parameter or a variable. It is allowed to create triggers for tables with`NCLOB`

columns with one exception, in an instead of trigger it is not possible to reference`NCLOB`

columns in the new table.## Collations

All national character strings have a collation attribute. A collation determines the order for ordering and comparisons, see Mimer SQL User's Manual, Collations for a detailed description of collations.

## Binary

The binary data type stores a sequence of bytes.

The binary data type category contains the following data types:

## Data Type

## Abbreviation

## Description

## Range

`BINARY(n)`

N/A Fixed length binary string, maximum length n. 1 <= n <= 15 000`BINARY VARYING(n)`

`VARBINARY(n)`

Variable length binary string, maximum length n. 1 <= n <= 15 000`BINARY LARGE OBJECT(n[K|M|G])`

`BLOB(n[K|M|G])`

Variable length binary string measured in octets. For information on the object length, see Specifying the BLOB Length.## Note: How binary data is displayed depends on the SQL tool used. For example, Mimer BSQL displays binary data as its hexadecimal value.

## BINARY LARGE OBJECT or BLOB

The

`BINARY LARGE OBJECT`

or`BLOB`

data type stores binary string values of varying length up to the maximum specified as the large object length (`n[K|M|G]`

).The large object length is

`n`

, optionally multiplied by`K|M|G`

.Data stored in

`BLOB`

's may only be stored in the database and retrieved again, it cannot be used in arithmetical operations.## Specifying the BLOB Length

If you specify

`<n>K`

, the length is`<n>`

multiplied by 1 024.If you specify

`<n>M`

, the length is`<n>`

multiplied by 1 048 576.If you specify

`<n>G`

, the length is`<n>`

multiplied by 1 073 741 824.If you do not specify large object length, Mimer SQL assumes that the length of the data type is 1M.

## Maximum BLOB Length

The maximum length of a

`BLOB`

is determined by the amount of disk space available for its storage.## Using BLOB's

You can work with

`BLOB`

's as follows:

- Retrieving
`BLOB`

's with simple column references in the`SELECT`

clause of a`SELECT`

statement- Assigning
`BLOB`

's using`INSERT`

statements with a`VALUES`

clause- Assigning
`BLOB`

's using`UPDATE`

statements- Adding
`BLOB`

columns using`CREATE TABLE`

or`ALTER TABLE`

- Dropping
`BLOB`

columns using`ALTER TABLE`

- Altering
`BLOB`

column data types using`ALTER TABLE`

There are some restrictions associated with using

`BLOB`

's. The only comparison supported for`BLOB`

values is using the`NULL`

predicate.The only scalar functions which can be used on

`BLOB`

columns are`SUBSTRING`

,`CHAR_LENGTH`

and`OCTET_LENGTH`

.A

`BLOB`

column may not be part of any primary key constraint, index, or unique constraint.The comparison restrictions also prevent

`BLOB`

columns from being used in`DISTINCT`

,`GROUP BY`

and`ORDER BY`

clauses and`UNION`

,`EXCEPT`

and`INTERSECT`

statements.When defining a stored procedure or trigger it is not allowed to use a

`BLOB`

type for a parameter or a variable. It is allowed to create triggers for tables with`BLOB`

columns with one exception, in an instead of trigger it is not possible to reference`BLOB`

columns in the new table.## Numerical

The numerical data type category contains the following data types:

All numerical data may be signed.

For all numerical data, the precision p indicates the maximum number of decimal digits the number may contain, excluding any sign or decimal point.

For decimal data, the scale s indicates the fixed number of digits following the decimal point.

## Note: The decimal data with scale zero DECIMAL(p,0) is not the same as integer INTEGER(p).

For

`FLOAT(p)`

, floating point (approximate numerical) data is stored in exponential form. The precision is specified for the mantissa only. The permissible range of the exponent is -999 to +999.## Specifying Data Type Precision and Scale

In the following cases, the omission of scale, or the omission of both precision and scale, is allowed (scale may not be specified without precision):

## Data Type

## Abbreviation

`DECIMAL`

`DEC`

is equivalent to`DECIMAL(15,0)`

`DECIMAL(5)`

`DEC(5)`

is equivalent to`DECIMAL(5,0)`

## Note: The data type INTEGER is distinct from INTEGER(10). (INTEGER(10) may store values between -9 999 999 999 and 9 999 999 999, but INTEGER may only store values between -2 147 483 648 and 2 147 483 647.)

## Datetime

`DATETIME`

is a term used to collectively refer to the data types`DATE`

,`TIME(s)`

and`TIMESTAMP(s)`

.

## Data type

## Description

`DATE`

`TIME(s)`

`TIMESTAMP(s)`

Composed of a number of integer fields, represents an absolute point in time, depending on sub-type.Default`s`

value is 0 for`TIME`

and 6 for`TIMESTAMP`

.## DATE

`DATE`

describes a date using the fields`YEAR`

,`MONTH`

and`DAY`

in the format`YYYY-MM-DD`

. It represents an absolute position on the timeline.## TIME(s)

`TIME(s)`

describes a time in an unspecified day, with seconds precision s, using the fields`HOUR`

,`MINUTE`

and`SECOND`

in the format`HH:MM:SS[.sF]`

where`F`

is the fractional part of the`SECOND`

value. It represents an absolute time of day.## TIMESTAMP(s)

`TIMESTAMP(s)`

describes both a date and time, with seconds precision`s`

, using the fields`YEAR`

,`MONTH`

,`DAY`

,`HOUR`

,`MINUTE`

and`SECOND`

in the format`YYYY-MM-DD HH:MM:SS[.sF]`

where`F`

is the fractional part of the`SECOND`

value. It represents an absolute position on the timeline.## DATETIME Significance

A

`DATETIME`

contains some or all of the fields`YEAR`

,`MONTH`

,`DAY`

,`HOUR`

,`MINUTE`

and`SECOND`

. These fields always occur in the order listed, which is from the most significant to least significant. Year is the most significant.Each of the fields is an integer value, except that the

`SECOND`

field may have an additional integer component to represent the fractional seconds.For a

`DATETIME`

value with a`SECOND`

component, it is possible to specify an optional seconds precision which is the number of significant digits in the fractional part of the`SECOND`

value. This must be a value between 0 and 9. If a`SECOND`

's precision is not specified, the default is 0 for`TIME`

and 6 for`TIMESTAMP`

.## Calendar and Clock

`DATE`

values are represented according to the Gregorian calendar.`TIME`

values are represented according to the 24 hour clock.## Inclusive Value Limits for DATETIME

The inclusive value limits for the DATETIME fields are as follows:

## Field

## Inclusive value limit

`YEAR`

0001 to 9999`MONTH`

01 to 12`DAY`

01 to 31 (upper limit further constrained by`MONTH`

and`YEAR`

)`HOUR`

00 to 23`MINUTE`

00 to 59`SECOND`

00 to 59.999999999## Interval

An

`INTERVAL`

is a period of time, such as: 3 years, 90 days or 5 minutes and 45 seconds.

## Data Type

## Description

`INTERVAL`

Composed of a number of integer fields, represents a period of time, depending on the type of interval.There are effectively two kinds of

`INTERVAL`

:

- YEAR-MONTH
containing one or both of the fields

`YEAR`

and`MONTH`

. (Also known as long interval.)- DAY-TIME
containing one or more consecutive fields from the set

`DAY`

,`HOUR`

,`MINUTE`

and`SECOND`

. (Also known as short interval.)The distinction is made between the two interval types in order to avoid the ambiguity that would arise if a

`MONTH`

value was combined with a field of lower significance, e.g.`DAY`

, given that different months contain differing numbers of days.For example, the hypothetical interval 2 months and 10 days could vary between 69 and 72 days in length, depending on the months involved. Therefore, to avoid unwanted variations in the downstream arithmetic etc. the variable length

`MONTH`

component may only exist at the lowest significance level in an`INTERVAL`

.The

`SECOND`

field may also only exist at the lowest significance level in an`INTERVAL`

, simply because it is the least significant of all the fields.An

`INTERVAL`

data type is a signed numeric quantity (i.e. negative`INTERVALs`

are allowed) comprising a specific set of fields. The list of fields in an`INTERVAL`

is called the interval precision.The fields in an

`INTERVAL`

are exactly the same as those previously described for`DATETIME`

except that the value constraints imposed on the most significant field are determined by the leading precision (p in Interval Qualifiers) for the`INTERVAL`

type and not by the Gregorian calendar and 24 hour clock.A leading precision value between 1 and the maximum allowed for the field type may be specified for an

`INTERVAL`

. If none is specified, the default is 2.## Value Constraints for Fields in an Interval

The table below shows the maximum permitted leading precision values for each field type in an

`INTERVAL`

:The value of a

`MONTH`

field, which is not in the leading field position, is constrained between 0 and 11, inclusive, in an`INTERVAL`

(and not between 1 and 12 as in a`DATETIME`

).Where the

`SECOND`

field is involved, seconds precision (s in Interval Qualifiers) can be specified for it in the same way as for`DATETIME`

.Note that in the

`INTERVAL`

consisting only of a`SECOND`

field (`INTERVAL SECOND`

), the`SECOND`

field will have both a leading precision and a seconds precision, specified together.A seconds precision value between 0 and 9 may be specified for an

`INTERVAL`

. If the seconds precision is not specified, a default value of 6 is implied.## Interval Qualifiers

A syntactic element, the interval qualifier, is used to specify the interval precision, leading precision and (where appropriate) the seconds precision.

The interval qualifier follows the keyword

`INTERVAL`

when specifying an`INTERVAL`

data type.The following table lists the valid interval qualifiers for YEAR-MONTH intervals:

The following table lists the valid interval qualifiers for DAY-TIME intervals:

## Length of an Interval Data Type

The length of an

`INTERVAL`

data type is the same as the number of characters required to represent it as a string and is determined by the interval precision, leading precision and the seconds precision (where it applies).The maximum length of an

`INTERVAL`

data type can be computed according to the following rules:

- The length of the most significant field is the leading precision value (p).
- Allow a length of 2 for each field following the most significant field.
- Allow a length of 1 for each separator between fields. Separators occur between
`YEAR`

and`MONTH`

,`DAY`

and`HOUR`

,`HOUR`

and`MINUTE`

, and`MINUTE`

and`SECOND`

.- If seconds precision applies, and is non-zero, allow a length equal to the seconds precision value, plus 1 for the decimal point preceding the fractional part of the seconds value.
## Boolean

`BOOLEAN`

describes a truth value. It can have the values`TRUE`

or`FALSE`

.## Spatial Data Types

The spatial data types can be used for geographical data (longitude, latitude and location), and for coordinate system data (x, y, coordinate).

The following user-defined types are used to store spatial data:

## Type

## SQL type

## Description

A distinct user-defined type that stores latitude values.

See Mimer SQL Programmer's Manual, Appendix 13, BUILTIN.GIS_LATITUDE. A distinct user-defined type that stores longitude values.

See Mimer SQL Programmer's Manual, Appendix 13, BUILTIN.GIS_LONGITUDE. A distinct user-defined type that is used to store a location on Earth. It has a latitude and a longitude component.

See Mimer SQL Programmer's Manual, Appendix 13, BUILTIN.GIS_LOCATION. This type has an x and a y component in a flat coordinate system.

See Mimer SQL Programmer's Manual, Appendix 13, BUILTIN.GIS_COORDINATE.See Mimer SQL Programmer's Manual, Appendix 13, Spatial Data for a description of the GIS (Geographic information system) functionality.

## ROW Data Type

There is an additional data type supported by Mimer SQL, called the

`ROW`

data type, which is used in stored procedures only.A variable which is declared as having the

`ROW`

data type implicitly defines a row value, which is a single construct that has a value which effectively represents a table row.A row value is composed of a number of named values, each of which has its own data type and represents a column value in the overall row value.

A

`ROW`

data type can be defined either by explicitly specifying a number of field-name/data-type pairs or by specifying a number of table columns from which the unqualified names and data types are inherited.A

`ROW`

data type definition can be specified where one of the above data types would normally be used in a variable declaration in a compound statement, see the Mimer SQL Programmer's Manual, The ROW Data Type, for details.## ROW Data Type Syntax

The syntax for defining a ROW data type is:

The following points apply to the specification of a

`ROW`

data type:

- A domain cannot be specified for
`data-type`

.- The value specified for
`data-type`

can be a`ROW`

data type specification.- Two fields in the same
`ROW`

data type specification must not have the same name (this restriction applies equally to fields named by specifying a`field-name`

value and those named by inheriting the unqualified name of a table column).- If
`table-name`

is specified without a list of column names, all the columns in the table are used to define fields in the`ROW`

data type.## The Null Value

Columns which contain an undefined value are assigned a null value.

Depending on the context, this is represented in SQL statements either by the keyword

`NULL`

or by a host variable associated with an indicator variable whose value is minus one, see the Mimer SQL Programmer's Manual, Indicator Variables.The null value is generally never equal to any value, not even to itself. All comparisons involving null evaluate to unknown, see Comparisons.

## Note: Null values are treated as equal to each other for the purposes of DISTINCT, GROUP BY, ORDER BY, UNION, INTERSECT and EXCEPT.

Null values are sorted at the end of ascending sequences and at the beginning of descending sequences.

## Data Type Compatibility

Assignment and comparison operations generally require that the data types of the items involved (literals, variables or column values) are compatible but not necessarily exactly equivalent.

Any exceptions to this rule are specified in the detailed syntax descriptions in SQL Statement Descriptions.

All character data is compatible with all other character data.

Numerical data is compatible with other numerical data regardless of specific data type (integer, decimal or float). Rules for operations involving mixed numerical data types are described in Comparisons.

Datetime and interval data types can be combined in arithmetic operations, for details, see Datetime and Interval Arithmetic.

Values stored in host variables (but not literals or column values) may be converted between character and numerical data types if required by the operation using the variable. The declared type of the variable itself is not altered.

Similarly, character columns may be assigned to numerical variables and vice versa. The rules for data type conversion are given below.

Variables may be converted between different data types by using the

`CAST`

function.## Datetime and Interval Arithmetic

The following table lists the arithmetic operations that are permitted involving

`DATE`

,`TIME`

,`TIMESTAMP`

(`DATETIME`

) or`INTERVAL`

values:Operands can not be combined arithmetically unless their data types are comparable, see Comparisons. If either operand is the null value, then the result will always be the null value.

If an arithmetic operation involves two

`DATETIME`

or`INTERVAL`

values with a defined scale, the scale of the result will be the larger of the scales of the two operands.When an

`INTERVAL`

value is multiplied by a numeric value, the scale of the result is equal to that of the`INTERVAL`

and the precision of the result is the leading precision of the`INTERVAL`

increased by 1. In the case of division, the same is true except that the precision of the result is equal to the leading precision of the`INTERVAL`

(i.e. it is not increased by 1).When two

`INTERVAL`

values are added or subtracted, the scale (s) and precision (p) of the result are described by the following rule:p = min(MLP, max(p'-s', p"-s") + max(s', s") + 1) s = max(s', s")where

`MLP`

is the maximum permitted leading precision for the`INTERVAL`

type of the result, refer to the table in Interval for these values.The interval precision of the result is the combined interval precision of the two operands, e.g.

DAY TO HOUR + MINUTE TO SECONDwill produce a

`DAY TO SECOND`

result.One

`DATETIME`

value may be subtracted from another to produce an`INTERVAL`

that is the signed difference between the stated dates or times.The application must, however, specify an

`INTERVAL`

date type for the result by using an`interval-qualifier`

.(DATETIME1 - DATETIME2) interval-qualifier(DATE '1996-01-09' - DATE '1996-01-01') DAYThis, therefore, evaluates to

`INTERVAL '8' DAY`

.## Data Types for Parameter Markers

Parameter markers in prepared SQL statements are assigned data types appropriate to their usage. See the Mimer SQL Programmer's Manual, Dynamic SQL, for a discussion of dynamic SQL.

For parameter markers used to represent data assigned to columns, the precision is assigned in accordance with the column definition.

## Host Variable Data Type Conversion

When a host variable is used in assignments, comparisons or expressions where the data type of the variable is different from the data type of literals or column declarations, an attempt is made internally to convert the value of the variable to the appropriate type.

## Character and Character

Conversion between a character variable and a character value is always allowed. The conversion follows these rules:

- When assigning a character value to a character variable, where the variable is longer than the character value, the variable is padded with trailing blanks.
- When assigning a character value to a character variable, where the value is longer than the variable, the value is truncated and a warning status is returned. If only blanks are truncated, no warning is returned.
- When assigning a variable length character, i.e. a
`VARCHAR`

or`NCHAR VARYING`

, column from a character variable, the column is padded with blanks up to the length of the character variable if the column is longer than the variable.- When assigning a variable length character column from a character variable, where the column is shorter than the variable (except for trailing spaces), the assignment will fail and an error message is returned.
## National Character and Character

- When assigning a national character column to a character variable, characters outside the Latin1 character set may occur.
- When assigning a character column to a wide character variable, all characters will be converted to the wide character format.
- When assigning a character column a national character value where characters outside the Latin1 character set occur, the assignment will fail and an error message is returned.
- When assigning a character value to a national character column, the value will be converted to the national character data type.
## Numerical and Character

Numerical values may always be converted to character strings, provided that the character string variable is sufficiently long enough. The resulting string format is illustrated below, using n to represent the appropriate number of digits and s to represent the sign position (a minus sign for negative values).

Two digits are always used for the exponent derived from REAL numbers, and three digits are used for all other floating point numbers, regardless of the value of the exponent. The sign of the exponent is always given explicitly (+ or -).

## Note: Decimal values with scale 0 are converted to strings with the format 'sn.'. Decimal values where the scale is equal to the precision result in strings with the format 's.n'.

## Examples of Assignment Results

## Value

## Type

## Character value

1342`INTEGER`

'1342' -15`INTEGER`

'-15' 13.42`DECIMAL(6,4)`

'13.4200' -13.`DECIMAL(5,0)`

'-13.' .13`DECIMAL(2,2)`

'.13' -1.3E56`DOUBLE PRECISION`

'-1.30000000000000E+056'Only numerical character strings can be converted to numerical data.

## Numerical strings are defined as follows:

- Integer
One optional sign character (+ or -) followed by at least one digit (0-9). Leading and trailing blanks are ignored. No other character is allowed.

- Decimal
As integer, but with one decimal point (.) placed immediately before or after a digit.

- Float
As decimal, but followed directly by an uppercase or lowercase letter E and an exponent written as an integer (optionally signed).

The precision and scale of a number derived from a numerical character string follows the format of the string.

Leading and trailing zeros are significant for assigning precision.

## Numerical value

## Type

3`INTEGER(1)`

003`INTEGER(3)`

0.3`DECIMAL(2,1)`

00.30`DECIMAL(4,2)`

.3 -33`INTEGER(2)`

-33.`DECIMAL(2,0)`

003.3E14`FLOAT(4)`

## Standard Compliance

This section summarizes standard compliance concerning data types.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se |