SAND CDBMS SQL Reference Guide
SAND CDBMS SQL Database Objects and Language Elements

SAND CDBMS Data Types

Characteristics of Numeric Data Types

Removal of Trailing Digits

If an SQL operation (for example, an INSERT or UPDATE) results in a value losing one or more least significant digits, the value is rounded.

Example

CREATE TABLE d1 (col1 DECIMAL(5,2));
INSERT INTO d1 VALUES (99.999);
SELECT col1 FROM d1;
col1
-------
 100.00

In this example, the value 99.999 is inserted into a column defined as DECIMAL(5,2). Since the scale of the inserted value exceeds the scale of the column, the value is rounded (that is, to 100.00) to fit the column.

Data Type Conversion

When a value of one data type is converted to another data type (for instance, using the INSERT...SELECT command), if any trailing digits are lost during the conversion process, the value is rounded.

Example

CREATE TABLE d1 (col1 DECIMAL(5,2));
INSERT INTO d1 VALUES (100.51);
CREATE TABLE s1 (col1 SMALLINT);
INSERT INTO s1 (col1) SELECT col1 FROM d1;
SELECT col1 FROM s1;
col1
------
   101

In the above example, a value from a DECIMAL column in one table is copied into a SMALLINT column in another table. Since the destination column cannot store decimal digits, the source value (100.51) is rounded to the nearest integer value (101).