## 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:

## 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:

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.## 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:

## 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.

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