Substract with decimal

Good afternoon,

I have a basic table with Crédit and Débit columns (INTEGER fields with 2 decimals).

When I sum up each column, the result is fine (I can see the right sum with decimal) :

  • 15363,51 for Crédit
  • 6672,32 for Débit

But when I substract Crédit with Débit, I get this result 869119 but I am expecting this result 8691,19. It’s like the result got multiplied by 100.

Does someone have an idea about the problem ? Is it a field type issue ?

Thank you for your help,
Hobbit

… (INTEGER fields with 2 decimals)

Ahem … what is a INTEGER field with 2 decimals? INTEGER does not have decimals (at least my Base does - mathematically correct - not allow to specify decimals for INTEGER). It looks like the decimal point is just ignored, since 1536351 - 667232 = 869119

Hello,

Your information is incomplete but your image does give some indications. First, I agree with @anon73440385 if these were Integer fields as stated. However, It appears they are not since the first two result fields (SUM & SUM) each show a decimal in the result.

This leads me to an educated guess that you are using a Firebird embedded database. If this is fact the case (should be noted in the question along with OS & LO version) then the calculation is a bug.

You can get a correct result if you CAST the calculated amount. Please see my answer in this post → Computed field in query (on Firebird in Libreoffice) and the link within that answer.

Thank you both of you for your answers !

@anon73440385, you are right, Integers should not work but my dev skills are way too rusted ! As the SUM was showing up decimal, I didn’t consider the variable type as potential issue.

@Ratslinger, correct, I am using a firebird DB. The Cast solved the issue :
SELECT Cast(SUM( “Crédit” ) - SUM( “Débit” ) As Decimal(5,2)) FROM “Operations”

Regarding my system information (for other users with the same potential problem) :
OS : Ubuntu 18.04.1 TLS
LO : 6.2.2.2

Thank you again for your support !