Exact Numerics (Language Reference Guide)

Go Up to SQL Statement and Function Reference

All NUMERIC and DECIMAL data types are stored as exact numerics: 16, 32, or 64 bits, depending on the precision. NUMERIC and DECIMAL data types with precision greater than 9 are referred to as large exact numerics.

• If one operand is an approximate numeric, the result of any dyadic operation (addition, subtraction, multiplication, division) is DOUBLE PRECISION.
• Any value that can be stored in a DECIMAL(18,S) can also be specified as the default value for a column or a domain.

Addition and Subtraction

If both operands are exact numeric, adding or subtracting the operands produces an exact numeric with a precision of 18 and a scale equal to the larger of the two. For example:

CREATE TABLE t1 (n1 NUMERIC(16,2), n2 NUMERIC(16,3));
INSERT INTO t1 VALUES (12.12, 123.123);
COMMIT;

The following query returns the integer 135.243. The largest scale of the two operands is 3; therefore, the scale of the sum is 3.

SELECT n1 + n2 FROM t1;

Similarly, the following query returns the integer -111.003:

SELECT n1 - n2 FROM t1;

If either of the operands is approximate numeric (FLOAT, REAL, or DOUBLE PRECISION), the result is DOUBLE PRECISION.

Multiplication

If both operands are exact numeric, multiplying the operands produces an exact numeric with a precision of 18 and a scale equal to the sum of the scales of the operands. For example:

CREATE TABLE t1 (n1 NUMERIC(16,2), n2 NUMERIC(16,3));
INSERT INTO t1 VALUES (12.12, 123.123);
COMMIT;

the following query returns the integer 1492.25076 because n1 has a scale of 2 and n2 has a scale of 3. the sum of the scales is 5.

SELECT n1*n2 FROM t1

If one of the operands is approximate numeric (FLOAT, REAL, or DOUBLE PRECISION), the result is DOUBLE PRECISION.

Division

If both operands are exact numeric, dividing the operands produces an exact numeric with a precision of 18 and a scale equal to the sum of the scales of the operands. If at least one operand of a division operator has an approximate numeric type (FLOAT, REAL, or DOUBLE PRECISION), the result is DOUBLE PRECISION.

For example, in the following table, division operations produce a variety of results:

CREATE TABLE t1 (i1 INTEGER), i2 INTEGER, n1 NUMERIC(16,2)
n2 NUMERIC(16,2));
INSERT INTO t1 VALUES (1, 3, 1.00, 3.00);
COMMIT;

The following query returns the integer 0 because each operand has a scale of 0, so the sum of the scales is 0:

SELECT i1/i2 FROM t1

The following query returns the NUMERIC(18,2) value 0.33, because the sum of the scales 0 (operand 1) and 2 (operand 2) is 2:

SELECT i1/n2 FROM t1

The following query returns the NUMERIC(18,4) value 0.3333, because the sum of the two operand scales is 4:

SELECT n1/n2 FROM t1

In InterBase 5 and earlier, any of the above division operations would have returned the DOUBLE PRECISION value 0.3333333333333333.

Advance To:

• Error Handling