8.10Special Functions for DECFLOAT

8.10.1COMPARE_DECFLOAT()

Available inDSQL, PSQL

Result typeSMALLINT

Syntax

  |COMPARE_DECFLOAT (decfloat1, decfloat2)

Table 8.77COMPARE_DECFLOAT Function Parameters
ParameterDescription

decfloatn

Value or expression of type DECFLOAT, or cast-compatible with DECFLOAT

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

0

Values are equal

1

First value is less than second

2

First value is greater than second

3

Values are unordered, i.e. one or both is NaN/sNaN

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

See alsoSection 8.10.4, “TOTALORDER()

8.10.2NORMALIZE_DECFLOAT()

Available inDSQL, PSQL

Result typeDECFLOAT

Syntax

  |NORMALIZE_DECFLOAT (decfloat_value)

Table 8.78NORMALIZE_DECFLOAT Function Parameters
ParameterDescription

decfloat_value

Value or expression of type DECFLOAT, or cast-compatible with DECFLOAT

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.

8.10.2.1Examples of NORMALIZE_DECFLOAT

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

8.10.3QUANTIZE()

Available inDSQL, PSQL

Syntax

  |QUANTIZE (decfloat_value, exp_value)

Table 8.79QUANTIZE Function Parameters
ParameterDescription

decfloat_value

Value or expression to quantize; needs to be of type DECFLOAT, or cast-compatible with DECFLOAT

exp_value

Value or expression to use for its exponent; needs to be of type DECFLOAT, or cast-compatible with DECFLOAT

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

Note

The target exponent is the exponent used in the Decimal64 or Decimal128 storage format of DECFLOAT of exp_value. This is not necessarily the same as the exponent displayed in tools like isql. For example, the value 1.23E+2 is coefficient 123 and exponent 0, while 1.2 is coefficient 12 and exponent -1.

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.

8.10.3.1Examples of QUANTIZE

   |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

8.10.4TOTALORDER()

Available inDSQL, PSQL

Result typeSMALLINT

Syntax

  |TOTALORDER (decfloat1, decfloat2)

Table 8.80TOTALORDER Function Parameters
ParameterDescription

decfloatn

Value or expression of type DECFLOAT, or cast-compatible with DECFLOAT

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

-1

First value is less than second

0

Values are equal

1

First value is greater than second.

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()