Mimer SQL Reference Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer


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

Numerical Assignments

Datetime Assignment Rules

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.

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 numbers are never truncated. Fractional parts of decimal and floating point 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 floating point.

When decimal or floating point values are converted to integers, the fractional part of the number is truncated (not rounded). Note that the range of numbers represented by integers is smaller than the range represented by floating point numbers. Assignment of a floating point number to an integer 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 The ROUND Function.

In converting decimal values to floating point, 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 floating point 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.

The following table illustrates the main features of numerical assignments.

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

Standard Compliance

This section summarizes standard compliance concerning assignments.

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