Available inDSQL, PSQL

Result typeSMALLINT

Syntax

  |COMPARE_DECFLOAT (decfloat1, decfloat2)

COMPARE_DECFLOAT compares two DECFLOAT values to be equal, different or unordered. The result is a SMALLINT value, as follows:

Unlike the comparison operators (<, =, >, etc.), comparison is exact: COMPARE_DECFLOAT(2.17, 2.170) returns 2 not 0.

See alsoSection 8.10.4, “TOTALORDER()”

Available inDSQL, PSQL

Result typeDECFLOAT

Syntax

  |NORMALIZE_DECFLOAT (decfloat_value)

NORMALIZE_DECFLOAT takes a single DECFLOAT argument and returns it in its simplest form. That means that for any non-zero value, trailing zeroes are removed with appropriate correction of the exponent.

  |-- will return 12
  |select normalize_decfloat(12.00)
  |from rdb$database;
  | 
  |-- will return 1.2E+2
  |select normalize_decfloat(120)
  |from rdb$database;

Available inDSQL, PSQL

Syntax

  |QUANTIZE (decfloat_value, exp_value)

QUANTIZE returns a DECFLOAT value that is equal in value and sign (except for rounding) to decfloat_value, and that has an exponent equal to the exponent of exp_value. The type of the return value is DECFLOAT(16) if both arguments are DECFLOAT(16), otherwise the result type is DECFLOAT(34).

If the exponent of decfloat_value is greater than the one of exp_value, the coefficient of decfloat_value is multiplied by a power of ten, and its exponent decreased, if the exponent is smaller, then its coefficient is rounded using the current decfloat rounding mode, and its exponent is increased.

When it is not possible to achieve the target exponent because the coefficient would exceed the target precision (16 or 34 decimal digits), either a Decfloat float invalid operation error is raised or NaN is returned (depending on the current decfloat traps configuration).

There are almost no restrictions on the exp_value. However, in almost all usages, NaN/sNaN/Infinity will produce an exception (unless allowed by the current decfloat traps configuration), NULL will make the function return NULL, and so on.

   |select v, pic, quantize(v, pic) from examples;
   | 
   |     V    PIC QUANTIZE
   |====== ====== ========
   |  3.16  0.001    3.160
   |  3.16   0.01     3.16
   |  3.16    0.1      3.2
   |  3.16      1        3
   |  3.16   1E+1     0E+1
   |  -0.1      1       -0
   |     0   1E+5     0E+5
   |   316    0.1    316.0
   |   316      1      316
   |   316   1E+1   3.2E+2
   |   316   1E+2     3E+2

Available inDSQL, PSQL

Result typeSMALLINT

Syntax

  |TOTALORDER (decfloat1, decfloat2)

TOTALORDER compares two DECFLOAT values including any special values. The comparison is exact, and returns a SMALLINT, one of:

For TOTALORDER comparisons, DECFLOAT values are ordered as follows:

  |-NaN < -sNaN < -INF < -0.1 < -0.10 < -0 < 0 < 0.10 < 0.1 < INF < sNaN < NaN

See alsoSection 8.10.1, “COMPARE_DECFLOAT()”