ESQL DECIMAL data type

The DECIMAL data type holds an exact representation of a decimal number. Decimals have precision, scale, and rounding. Precision is the total number of digits of a number:
  • The minimum precision is 1
  • The maximum precision is 34
Scale is the number of digits to the right of the decimal point:
  • The minimum scale (-exponent) is -999,999,999
  • The maximum scale (-exponent) is +999,999,999

You cannot define precision and scale when declaring a DECIMAL, because they are assigned automatically. It is only possible to specify precision and scale when casting to a DECIMAL.

Scale, precision, and rounding

The following scale, precision, and rounding rules apply:
  • Unless rounding is required to keep within the maximum precision, the scale of the result of an addition or subtraction is the greater of the scales of the two operands.
  • Unless rounding is required to keep within the maximum precision, the scale of the result of a multiplication is the sum of the scales of the two operands.
  • The precision of the result of a division is the smaller of the number of digits needed to represent the result exactly and the maximum precision.
  • All addition, subtraction, multiplication, and division calculations round the least significant digits, as necessary, to stay within the maximum precision
  • All automatic rounding is banker's or half even symmetric rounding. The rules of this are:
    • When the first dropped digit is 4 or less, the first retained digit is unchanged
    • When the first dropped digit is 6 or more, the first retained digit is incremented
    • When the first dropped digit is 5, the first retained digit is incremented if it is odd, and unchanged if it is even. Therefore, both 1.5 and 2.5 round to 2 while 3.5 and 4.5 both round to 4
    • Negative numbers are rounded according to the same rule

Decimal literals

Decimal literals that consist of an unquoted string of digits only, that is, that contain neither a decimal point nor an exponent (for example 12345) are of type INTEGER if they are small enough to be represented as integers. Otherwise they are of type DECIMAL.

Decimal literals that consist of an unquoted string of digits, optionally a decimal point, and an exponent (for example 123e1), are of type FLOAT if they are small enough to be represented as floats. Otherwise they are of type DECIMAL.

Decimal literals that consist of the keyword DECIMAL and a quoted string of digits, with or without a decimal point and with or without an exponent, are of type DECIMAL, for example, DECIMAL '42', DECIMAL '1.2346789e+203'.

The strings in this type of literal can also have the values:
  • 'NAN', not a number
  • 'INF', 'INFINITY'
  • '+INF', '+INFINITY'
  • '-INF', '-INFINITY'
  • 'MAX'
  • 'MIN'
(in any mixture of case) to denote the corresponding values.
Note, if you do not specify sufficient precision digits, that INF is returned, as shown in the following example:
 SET VAL = CAST('123456' AS DECIMAL(3,0))