in CDS View, casting decimal value to float leads to wrong decimals

[Kimmo_from_Finland]

I am making a custom CDS View in S/4 HANA onPremise edition, where I want devide 2 fields and multiply result with another field. Fields are of custom dictionary type, which refers to a domain of currency type. Meaning these fields are 23 of length with 2 decimals.

Consider a CDS view column definition like this:

( a / b * c ) as d //my_calculated_column.

firstly, when using / operator, the operators needs to be casted to float.

So I use this:

( ( cast( a as abap.float) / cast( b as abap.float ) ) * cast( c as abap.float ) ) as d.

the problem is, that casting to float changes the decimals to be incorrect.

for example if column a has value 3497,97 after casting to float it becomes all the sudden to 3497,96999999... and same with other operators

Same can be achieved with constants eg:

cast( 3497.97 as abap.fltp) as fixed_of_f returns 3497,96999999.

causing the calculated result to be wrong compared to operators with real 2 decimal values.

For example:

If a = 3497,97 and b= 3497,97 and c=524,70 the result d via castings is 524,69 when it should be 524,70, because 3497,97 / 3497,97 = 1. 

Casting the result to decimal is not possible, because fltp cannot be casted to any format.

I tried using function fltp_to_dec, which is not working when using casted result as parameter because it expects only a column. To avoid this, I created another CDS view, that uses this first CDS view as source and in second CDS view I used fltp_to_dec to convert my calculation result field to 2 decimal value, but it does not round it but just cuts to decimal leading the results being still wrong (eg 524,69 with above a, b, c values)

this is how second CDS view fields would be defined:

define view <second view name> as select from <first CDS view>

{

...

fltp_to_dec( c as abap.dec(23,2)) as c_dec

fltp_do_dec(d as abap.dec(23,2)) as d_dec.

Besides as wanted to use this CDS view in SE16 (yes, SAPGUI based), SE16 did not allowed to view fields of float type at all, so converting float fields to DEC was mandatory.

Also, using the Round function to round the calculated result to 2 decimals is not applicable either. That is because Round function does not accept input of type float.

A kind of mitigation is this:

Using DIVISION function instead of '/' . It would work for division at least, but when adding the '*' (multiply) operator, like this:

( DIVISION(a , b, 2) * c ) as d

ADT gives an error message 'Maximum accuracy 37 at DEC exceeded by an arithmetic expression' and that is because operator c being a currency with length 23 ( I tried with fixed value 0.15 and then it worked).

casting the operator c also to decimal(10,2) then works:

( DIVISION(a , b, 2) * casting( c as abap.dec(10,2) ) as d

With this solution I no longer need second CDS view that used ftlp_to_dec function. 

So in the first place, why is casting to float changes the decimals? I am just thinking this could happen in any applications, even standard CDS views, that does casting to float in order to perform division calculation with '/' operator. Why would anyone use cast ( <column> as abap.float) if decimals are not kept intact?

[Sandra_Rossi]

It's a very well-known problem due to the storage of binary floating-point numbers, they can't store exactly all decimal fractions. It's binary, so it can store precisely the fractions 0.5, 0.25, 0.125 (anything which divides 1 by a multiple of 2), or 0.75, 0.625 (a sum of several numbers which divide 1 by a multiple of 2). Conclusion: never cast/convert any currency amount to binary floating-point number. Instead, use decimal floating-point numbers (I don't know how it's supported in CDS views).

[RaymondGiuseppi]

Did you check ABAP CDS - Conversion Functions for Units and Currencies before casting amount/price to floating numbers?

[Kimmo_from_Finland]

As well as Sandra says: "never cast any currency to float" should we also say, that never use '/' operator because it forces operands must be float type and use DIVISION instead? Using '/' operator is so common in ABAP, so I'd guess many would make this same mistake when trying their first "code pushback" in CDS view. I even consider, that ADT should give a warning "be aware for decimals behavior" if CDS view is using '/' operator?

[Sandra_Rossi]

Conclude yourself: there may be a loss if a decimal number with a fraction is converted to a binary floating-point number (also numbers with more than something like 15 representative numbers for 8-byte binary floats).

[Sandra_Rossi · Accepted solution]

SOLUTION by the original poster: CAST CURR to DEC, then DEC to DECFLOAT, and you can then divide.

NOT A SOLUTION: In ABAP 7.58, CAST of CURR to DECFLOAT isn't possible (compile error message: "CAST operand_1 of type CURR to type DECFLOAT34 is not possible"):

CAST( operand_1 AS decfloat34 ) / CAST( operand_2 AS decfloat34 )

Concerning the packed types, only DEC and QUAN may be casted to DECFLOAT (16 or 34). https://help.sap.com/doc/abapdocu_754_index_htm/7.54/en-US/index.htm?file=abencds_f1_arithmetic_expr...

[Kimmo_from_Finland · Accepted solution]

Sandra, good hint, I thought abap.float was the only option. However, when operand is of type Currency, this does not work. ADT says, Cast <field> of type CURR to type DECFLOAT16/32 is not possible. What I did, that before casting my column to decfloat32 I used additional cast to dec(10,2). Like this: (cast(cast( operand1 as abap.dec(10,2) as abap.decfloat32 )) / (cast(cast(operand2 as abap.dec(10,2) as abap.decfloat32 )) as my_result

[Sandra_Rossi · Accepted solution]

Cast to decimal floating-point numbers is not supported in old ABAP version. This compiles as of ABAP 7.58: @AbapCatalog.sqlViewName: 'ZZSRO_CAST_CURR' @AbapCatalog.compiler.compareFilter: true @AccessControl.authorizationCheck: #NOT_REQUIRED @EndUserText.label: 'Test whether it''s possible or not' define view zzsro_cast_curr_to_decfloat34 as select from sbook { ( cast( forcuram as abap.decfloat34 ) / loccuram ) as CurrencyExchangeRate }.

[Sandra_Rossi · Accepted solution]

It's 16 or 34. CAST DECFLOAT is supported since ABAP 7.54. Could you post your solution in a separate answer; I will link my answer to yours, concerning workaround before 7.54. Thank you.

[Sandra_Rossi · Accepted solution]

IMPORTANT UPDATE. Sorry, the solution isn't valid, it doesn't compile in ABAP 7.58.

[Sandra_Rossi · Accepted solution]

This compiles in ABAP 7.58:

@AbapCatalog.sqlViewName: 'ZZSRO_CAST_CURR'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Cast CURR to DECFLOAT34 (via DEC)'
define view zzsro_cast_curr_to_decfloat34
  as select from sbook
{
  ( cast( cast( loccuram as abap.dec(15,2) ) as abap.decfloat34 )
  / cast( cast( forcuram as abap.dec(15,2) ) as abap.decfloat34 )) as CurrencyExchangeRate
}

[Kimmo_from_Finland · Accepted solution]

Eh, a little out of original issue, but now when wanted make some additional test using multiply operator '*' and came up with issues with fixed values. Why this does not work? (cast( <currency field> as abap.dec(10,2)) * 0.15) ADT says "at least one operand is of type FLOAT. This requires explicit float". It is the fixed value 0.15 causing this because if using integer operand 2 then no error. Then if trying to cast like this cast( <currency field> as abap.dec(10,2)) * (cast( 0.15 as abap.dec(10,2)) I get different error "cast 0.15 of type fltp to type dec is not permitted". I could use cast( 0.15 as abap.fltp), but then the decimals gets changed again (0,15 becomes 0,14). Only way to manage this simple multiply is by using integer 15 and divide it by 100 like this: division( ( ( <currency field ) * 15 ), 100, 2 ) as curr_times15perc. So point is that fixed value with decimals are treated as float.

[Sandra_Rossi · Accepted solution]

Just look in the documentation (link given above). "A numeric literal with a decimal point is floating point number of type FLTP" and it doesn't indicate any casting from FLTP but there is "FLTP_TO_DEC( arg AS dtype )" (https://help.sap.com/doc/abapdocu_754_index_htm/7.54/en-US/index.htm?file=abencds_f1_conv_func_types...).

[Kimmo_from_Finland · Accepted solution]

Sandra, regarding your advice on how to convert literal 0.15 to dec via fltp_to_dec function, that does not work, because this statement fltp_to_dec( 0.15 as abap.dec(10,2)) as fixed15 would change the literal 0.15 to 0,14 meaning if using that result with multiply operator, then results would be incorrect.

[Kimmo_from_Finland · Accepted solution]

As overall conclusion, I consider "code pushback" with operators multiply (*) and divide (/) or with numeric literals having decimals are tricky and one could easily make a mistake as operators require float/decfloat types where a change of decimals occurs. I think in future I will not use code pushback with these operators. I will select Sandra's post where she referred my self-made solution as Answer to this thread.

[Sandra_Rossi · Accepted solution]

Thanks for the feedback. It's worth a blog post when a decimal field is sourcing from a binary floating-point number field, a division or a numeric literal with decimal point.

[Kimmo_from_Finland]

Raymond, no I did not checked that before my posting, but did know they existed. When now checked those functions, I recon they'd not provide any help to actual issue that converting decimal values to float leads to decimals being wrong as the first helping person says.