DECFLOAT_SORTKEY scalar function

The DECFLOAT_SORTKEY function returns a binary value that can be used when sorting DECFLOAT values. The sorting occurs in a manner that is consistent with the IEEE 754R specification on total ordering.

The schema is SYSIBM.

decfloat-expression: An expression that returns a DECFLOAT value.
decfloat-expression can also be a character string or graphic string data type. The string input is implicitly cast to a numeric value of DECFLOAT(34).

The result is a fixed length binary string with a length attribute of 9 if decfloat-expression is a DECFLOAT(16) value or 17 if decfloat-expression is a DECFLOAT(34) value.

The result can be null; if the argument is null, the result is the null value.

Example: Assume that the following CREATE TABLE statement is used to create a table with a column that contains DECFLOAT values and the INSERT statements are used to populate the table:

   CREATE TABLE T1(D1 DECFLOAT(16));
   INSERT INTO T1 VALUES (2.100);
   INSERT INTO T1 VALUES (2.10);
   INSERT INTO T1 VALUES (2.1000);
   INSERT INTO T1 VALUES (2.1);

Then the following SELECT statement is used to return the values from D1:

   SELECT D1 FROM T1 ORDER BY D1;

The SELECT statement returns the following values, but because all numbers in the column have the same value, the ORDER BY clause has no effect and the values are returned in an arbitrary order:

   D1
   ---------

   2.1
   2.1000
   2.10
   2.100

The following SELECT statement, which includes the DECFLOAT_SORTKEY function in the ORDER BY clause, returns the properly ordered values:

   SELECT D1 
      FROM T1 
      ORDER BY (DECFLOAT_SORTKEY(D1));

   D1
   --------

   2.1000
   2.100
   2.10
   2.1