8.10 Special Functions for DECFLOAT
8.10.1 COMPARE_DECFLOAT()
Available inDSQL, PSQL
Result typeSMALLINT
Syntax
COMPARE_DECFLOAT (decfloat1, decfloat2)
COMPARE_DECFLOAT
Function ParametersParameter  Description 

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

Values are equal 

First value is less than second 

First value is greater than second 

Values are unordered, i.e. one or both is 
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.2 NORMALIZE_DECFLOAT()
Available inDSQL, PSQL
Result typeDECFLOAT
Syntax
NORMALIZE_DECFLOAT (decfloat_value)
NORMALIZE_DECFLOAT
Function ParametersParameter  Description 

decfloat_value  Value or expression of type 
NORMALIZE_DECFLOAT
takes a single DECFLOAT
argument and returns it in its simplest form.
That means that for any nonzero value, trailing zeroes are removed with appropriate correction of the exponent.
8.10.2.1 Examples 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.3 QUANTIZE()
Available inDSQL, PSQL
Syntax
QUANTIZE (decfloat_value, exp_value)
QUANTIZE
Function ParametersParameter  Description 

decfloat_value  Value or expression to quantize;
needs to be of type 
exp_value  Value or expression to use for its exponent;
needs to be of type 
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)
.
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.1 Examples 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.4 TOTALORDER()
Available inDSQL, PSQL
Result typeSMALLINT
Syntax
TOTALORDER (decfloat1, decfloat2)
TOTALORDER
Function ParametersParameter  Description 

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

First value is less than second 

Values are equal 

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