Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


Assignments


The following sections explain the rules that apply when values are assigned in SQL statements to database columns or to host variables.

String Assignments

If a string value assigned to a fixed-length or variable-length character column is longer than the defined length of the column (except for trailing spaces), the assignment will fail and an error is returned.

If a string value assigned to a fixed-length character column is shorter than the defined length of the column, the content of the column is padded to the right with blanks after the assignment.

If a string value assigned to a variable-length character column is shorter than the defined maximum length of the column, no blank padding occurs.

Character (both fixed length and variable length) column values assigned to fixed-length host variables in SQL statements are padded with blanks to the right if necessary. Column values assigned to host variables are truncated if they are longer than the declared length of the variable, and a warning is issued.

The following table summarizes the rules for character string assignment:

Assignment
Source too long
Source too short
To column
Error if non-blank character would be truncated.
Pad right with blanks for fixed-length columns. No blank padding for variable length columns.
To variable
Truncate and warn.
Pad right with blanks for fixed-length variables. No blank padding for variable length variables.

Numerical Assignments

Numbers assigned to columns or host variables assume the data type of the item to which they are assigned, regardless of the data type of the source.

Integral parts of INTEGER, DECIMAL or FLOAT values are never truncated. Fractional parts of DECIMAL and FLOAT numbers may be truncated if required. No precision is lost when converting INTEGER values to DECIMAL, but this may happen when converting INTEGER values to FLOAT.

When DECIMAL or FLOAT values are converted to INTEGER, the fractional part of the number is truncated (not rounded). Note that the range of numbers represented by DECIMAL and INTEGER is smaller than the range represented by FLOAT. Assignment of a FLOAT number to an INTEGER or DECIMAL produces an overflow error if the source number is too large.

In assigning DECIMAL values to DECIMAL targets, the length of the integer part of the source (i.e. the difference between the precision and scale) may not exceed the precision of the target. The necessary number of leading zeros is appended or eliminated, and trailing zeros are added to or digits truncated from the fractional part as required.

Note: Truncation effects can be avoided by explicitly using the ROUND function, see ROUND.

In converting DECIMAL values to FLOAT, the mantissa of the target is treated as a decimal number with the same precision as the source (for example, 1234.56 becomes 1.23456E3).

In converting FLOAT values to DECIMAL, digits are truncated from the fractional part of the result as required by the scale of the target. An overflow error occurs if the precision of the target cannot accommodate the integral part of the result.

When converting INTEGER, DECIMAL or FLOAT numbers to REAL or DOUBLE PRECISION, a rounding operation is often required. The number will be rounded to the nearest binary floating point representation (rounding to even if there is a tie). Note that such rounding is necessary for simple decimal numbers such as 0.1 which cannot be represented exactly as a binary floating point number.

When converting a REAL or DOUBLE PRECISION number to INTEGER, DECIMAL or FLOAT, the value will be rounded to the nearest number that is possible to represent in the target.

The following table illustrates the main features of numerical assignments:

Source:
Target:
INTEGER
SMALLINT
DECIMAL(9,2)
FLOAT(8)
REAL
INTEGER(6):
987654

987654

Overflow

987654.00

9.8765400E5

9.87654000E5
DECIMAL(6,3):
987.654

987

987

987.65

9.8765400E2

9.87654000E5
FLOAT(6):
9.87654E5

987654

Overflow

987654.00

9.8765400E5

9.87654000E5
FLOAT(6):
9.87654E49

Overflow

Overflow

Overflow

9.8765400E49

Overflow
FLOAT(6):
9.87654E-49

0

0

0.00

9.8765400E-49

0.0E0
REAL:
0.3E0

0

0

0.30

3.0000001E-001

3.00000012E-01

Leading zeros are shown where appropriate to indicate the maximum number of digits available. Leading zeros in numerical data are not normally displayed on output.

Datetime Assignment Rules

The following compatibility rules apply when assigning DATETIME values to one another:

Interval Assignment Rules

The following compatibility rules apply when assigning INTERVAL values to one another:

Binary Assignment Rules

A binary value assigned to a fixed-length binary column must have the same length as the defined length of the column, otherwise the assignment will fail and an error is returned.

If a binary value assigned to a variable-length binary column is shorter than the defined maximum length of the column, current length is set for the column.

If a binary value assigned to a variable-length binary column is longer than the defined maximum length of the column, the assignment will fail and an error is returned.

Binary (both fixed length and variable length) column values assigned to fixed-length host variables in SQL statements are padded with null values to the right if necessary. Column values assigned to host variables are truncated if they are longer than the declared length of the variable, and a warning is issued.

The following table summarizes the rules for binary string assignment:

Assignment
Source too long
Source too short
To column
Error.
Error for fixed-length columns. Current length set for variable length columns.
To variable
Truncate and warn.
Pad right with null values for fixed-length variables. No null value padding for variable length variables.

Boolean Assignment Rules

The BOOLEAN type can be assigned boolean values, i.e. TRUE and FALSE.

Note: Do not enclose boolean literals in string delimiters. 'TRUE' is a string literal, not a boolean literal.

Standard Compliance

This section summarizes standard compliance concerning assignments.

Standard
Compliance
Comments
SQL-2016
Core
Fully compliant.


Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX