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.

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 Mimer SQL Reference Manual, Standard Compliance.

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.

Source:	Target: INTEGER	SMALLINT	DECIMAL(9,2)	FLOAT(8)	REAL
INTEGER(6): 987654	0000987654	Overflow	0987654.00	9.8765400E5	9.876540E5
DECIMAL(6,3): 987.654	0000000987	00987	0000987.65	9.8765400E2	9.876540E2
FLOAT(6): 9.87654E5	0000987654	Overflow	0987654.00	9.8765400E5	9.876540E5
FLOAT(6): 9.87654E49	Overflow	Overflow	Overflow	9.8765400E49	Overflow
FLOAT(6): 9.87654E-49	0000000000	00000	0000000.00	9.8765400E-49	Underflow

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:

• If the value to be assigned is a DATE, the target must also be a DATE.
• If the value to be assigned is a TIME, the target must also be a TIME.
• If the value to be assigned is a TIMESTAMP, the target must also be a TIMESTAMP.
• The CAST function can be used in order to cross-assign.

Interval Assignment Rules

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

• When assigning a non-NULL value to an INTERVAL column, the leading precision of the target must be sufficient to represent the value.
• All YEAR-MONTH INTERVAL values are compatible with one another.
• All DAY-TIME INTERVAL values are compatible with one another.

Standard Compliance

This section summarizes standard compliance concerning assignments.

Standard	Compliance	Comments
SQL-99	Core	Fully compliant.
SQL-99	Features outside core	Feature F052, "Intervals and datetime arithmetics" support for INTERVAL.

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