ABAP News for Release 7.51 – Floating Points in Open SQL and ABAP CDS

All database platforms support binary floating point numbers. The corresponding built-in data of the ABAP Dictionary is FLTP. The problem is that the handling of binary floating point numbers is platform specific. Operations that are carried out on the database with binary floating point numbers can yield different results. In former times this mainly affected the aggregate function AVG of Open SQL. Nowadays, in the times of “code push down”, there are more situations, where FLTP numbers are not merely stored in database fields but are operands or results of operations, as e.g. SQL expressions or SQL functions in Open SQL and ABAP CDS. For all such operations involving binary Floating point numbers you must take the platform dependency into account.

There’s one exception. The CAST operator of the Open SQL and ABAP CDS CAST expression should have a platform  independent behavior. Therefore, it is not allowed to cast from FLTP to any other numeric type because there would be platform dependent results for types where you might not expect it.

On the other hand, there are enough use cases where you want to convert FLTP to other numeric types on the database. Therefore, with release 7.51, ABAP offers the following special conversion options for  binary floating point numbers:

• An new addition AS dtype to aggregate function AVG allows you to define the result type of this function in Open SQL and in ABAP CDS. The result type can be DEC, CURR, QUAN with given length or decimals. You can also specify FLTP explicitly. And the addition also allows you to calculate averages of arguments with data type INT8, which is not possible in the basic form.
• An new built-in function FLTP_TO_DEC allows you to convert an argument of type FLTP to DEC, CURR, QUAN with given length or decimals. This function is available in ABAP CDS but not in Open SQL yet.

The result of both new features can be platform dependent. The function FLTP_TO_DEC of ABAP CDS can be especially used to convert numeric literals with a decimal point to DEC. Unfortunately, ABAP CDS treats such a literal as type FLTP otherwise.

Open SQL Example

SELECT FROM sbook
       FIELDS fldate,
              AVG( luggweight ) AS av_luggweight_fltp,
              AVG( luggweight AS DEC( 8,4 ) ) AS av_luggweight_dec
       WHERE carrid = 'LH' AND
             connid = '0400'
       GROUP BY fldate
       INTO TABLE @DATA(result).

cl_demo_output=>display( result ).

The example demonstrates the aggregate function AVG without and with an explicit type specification. The output is something like:

ABAP CDS Example

@AbapCatalog.sqlViewName: 'KELLERHDEMO'
define view Kellerh_Demo
  as select from
    demo_expressions
    {
      1.234                                  as literal,
      fltp_to_dec( 1.234 as abap.dec(10,3) ) as dec_literal,
      fltp_to_dec( fltp1 as abap.dec(10,3) ) as dec1_10_3,
      fltp_to_dec( fltp2 as abap.dec(10,3) ) as dec2_10_3
    }
    where
      id = 'X'

The result can be something like:

Note that decimals are cut but not rounded here.

And what about DECFLOATS?

This blog is about binary floating point numbers and their platform dependency. Now you might ask where are the decimal floating point numbers that were introduced into the ABAP language as built-in data types decfloat16 and decfloat34 with release 7.02? Well, although they follow a standard (IEEE-754-2008) and overcome many of the difficulties of the binary floating point numbers, they are not natively supported by all platforms, neither for application server nor for database. And that’s why the ABAP Dictionary cannot offer real built-in DECFLOAT types. The existing types DF16_DEC, DF34_DEC, DF16_RAW and DF34_RAW are mainly for storing and reading decimal floating point numbers. Limited operations are possible with DF16_DEC and DF34_DEC in Open SQL but none in ABAP CDS (besides simple reads, of course). The hope that decimal floating point numbers sooner ar later will replace the binary floating  point numbers has not come true yet. And therefore we continue to live with workarounds.

For more information, see:

• AVG AS in Open SQL
• AVG AS in ABAP CDS
• FLTP_TO_DEC in ABAP CDS