Android Question Strange problem

[Alex_197]

I have 2 records in the database (see the attached image). I need to show the sum of Payment field and the max of PaidDate field.

SELECT
sum(PaidAmount) As PaidAmount,
Max(PaidDate) As PaidDate
FROM tblBilling
where WeekEndDate='2024-11-16T00:00:00'

The result is shown in the attached screenshot.

But when I run the same sql statement in my code it returns me PaidAmount=503.41999999999996 instead of 503.42.

Why am I getting this result?

 

[BlueVision]

Hi Alex,

This looks a lot like a rounding problem in floating point arithmetic to me.
Check your variable declarations.
If this is the problem, I would recommend an explicit variable declaration to avoid such rounding problems.

I could imagine that the problem might also arise when passing a variable into a table or list and you somehow define the placeholder for this variable in the table with a default value (double, float). If you retrieve the value later, it has changed because it has been transformed. To be on the safe side, save the variable as a string, retrieve this string as a string and convert it to your desired format.

Of course, you can also try rounding. But that might not produce the desired results either, I imagine.

It depends on your code and the variable declarations, I'm sure.

I had a similar problem with floating point arithmetic when calculating the orbit of the moon in my compass programme. The rounding errors at the beginning of a 43-step calculation routine were extremely high, which led to a deviation of 10 degrees. And the error was only somewhere far back in the decimal places...

Edit:
You mentioned a SQL-Database. What is the format of the target cell in which you save the value?

 

[Jeffrey Cameron]

Start with this post: https://www.b4x.com/android/forum/threads/solved-problem-with-float-variable.118163/#post-739437

 

[Alex_197]

Hi Alex,

This looks a lot like a rounding problem in floating point arithmetic to me.
Check your variable declarations.
If this is the problem, I would recommend an explicit variable declaration to avoid such rounding problems.

I could imagine that the problem might also arise when passing a variable into a table or list and you somehow define the placeholder for this variable in the table with a default value (double, float). If you retrieve the value later, it has changed because it has been transformed. To be on the safe side, save the variable as a string, retrieve this string as a string and convert it to your desired format.

Of course, you can also try rounding. But that might not produce the desired results either, I imagine.

It depends on your code and the variable declarations, I'm sure.

I had a similar problem with floating point arithmetic when calculating the orbit of the moon in my compass programme. The rounding errors at the beginning of a 43-step calculation routine were extremely high, which led to a deviation of 10 degrees. And the error was only somewhere far back in the decimal places...

Thank you for your reply.

The variable PaidAmount declared as Double, the field itself in the database is Decimal(10,2).

 

[Alex_197]

Start with this post: https://www.b4x.com/android/forum/threads/solved-problem-with-float-variable.118163/#post-739437

Thank you for your reply.
The variable PaidAmount declared as Double, the field itself in the database is Decimal(10,2).

 

[BlueVision]

When it comes to SQL, you have (as far as I know) two options for defining a field for numbers within a table. These options are INT and REAL.

As far as I know, there are only these two options with SQL, but I would be happy to be corrected.
If the field is defined as REAL, your value is automatically converted into a real value when it is stored in this cell. I suspect that this is where the rounding error occurs.
Can you have a look into the table with an SQL-Viewer directly after filling the cell?
However, the error can just as easily occur when the value is retrieved from the SQL table. Display it directly afterwards with a log command.

Edit:
A simple but somewhat unusual way would be to save the amounts as cent amounts. In other words, multiply them by 100, save them as integers and divide them by 100 again after retrieving them. However, this may again cause problems with small cent amounts. Use then the proper format in defining the cell (SQL_INT). Personally, I would save the value as a string (SQL_TEXT) and convert it to the desired decimal number format after retrieving it from the database. (Decimal 10,2).

 

[Alex_197]

When it comes to SQL, you have (as far as I know) two options for defining a field within a table. These options are INT and REAL.

As far as I know, there are only these two options with SQL, but I would be happy to be corrected.
If the field is defined as REAL, your value is automatically converted into a real value when it is stored in this cell. I suspect that this is where the rounding error occurs.
Can you have a look into the table with an SQL-Viewer directly after filling the cell?
However, the error can just as easily occur when the value is retrieved from the SQL table. Display it directly afterwards with a log command.

Field is Decimal(10,2)
Log that show a value directly from a database shows the same problem 503.41999999999996

I solved it by using Round2(PaymentAmount,2)

What I have noticed that if the only 1 record meets the search criteria it works correctly. But if it more than 1 record - I have this problem.

Like for example

One record with PaymentAmount 1432.55 works correctly with weekend date 2024-11-23T00:00:00
Two records 322.19 and 181.23 and we have a problem with weekend date 2024-11-16T00:00:00

The only difference is a different weekend date.

SELECT
sum(PaidAmount) As PaidAmount,
Max(PaidDate) As PaidDate
FROM tblBilling
where WeekEndDate='2024-11-16T00:00:00'

 

[BlueVision]

Have a look, I edited my previous post.
Do you display the table directly in your programme or do you display the values of the SQL database in a B4X table?
I it is a B4X table I would recommend to work with ticks-values when it comes to date and time. Then you are on the safe site.

 

[Alex_197]

Have a look, I edited my previous post.
Do you display the table directly in your programme or do you display the values of the SQL database in a B4X table?
I it is a B4X table I would recommend to work with ticks-values when it comes to date and time. Then you are on the safe site.

Directly. Just from the resultset.

 

[BlueVision]

So this is as I expected. You put your decimal formatted value into a REAL-field of the SQL-database. In this moment your value will be transformed into a real value.

 

[Alex_197]

So this is as I expected. You put your decimal formatted value into a REAL-field of the SQL-database. In this moment your value will be transformed into a real value.

The field type is Decimal(10,2) not REAL.

CREATE TABLE "tblBilling" (
    "ID"    INTEGER NOT NULL,
    "ClientID"    INTEGER,
    "ProviderID"    INTEGER,
    "WeekEndDate"    SmallDateTime,
    "TotalHours"    Decimal(10, 2),
    "ActualTotalHours"    Decimal(10, 2),
    "PaidAmount"    Decimal(10, 2),
    "PaidDate"    SmallDateTime,
    "IsAdvancePaid"    BIT,
    "MatchupID"    INT,
    PRIMARY KEY("ID" AUTOINCREMENT)
);

 

[BlueVision]

Directly. Just from the resultset.

Ok, makes no difference you can convert the ticks. But this is not your problem. Decision is yours how to store your data.

When it comes to the point of prevent rounding errors when transforming a monetary value, I would suggest these two options:
1. store cent values in an INT-field of the database or
2. store the value as a string and convert it after picking to decimal (10,2)

I don't know exactly how SQL handles that decimal declaration. Is probably a formatted real value, so this would not help in your case. (automatically rounding)

So, keep it simple, option 1 is my recommendation.

 

[emexes]

if the only 1 record meets the search criteria it works correctly

Well, it looks like it works correctly, but if the cents value is anything other than 0, 25, 50 or 75 cents, then it will not actually be precisely correct.

 

[klaus]

These are normal display problems with Double numbers.
A digital number cannot hold the exact value of 503.42, but only the closest one 503.41999999999996.
To display the result use NumberFormat or NumberFormat2.
Try this code:

    Private a, b As Double
    a = 322.19
    b = 181.23
    Log(a + b)

You get: 503.41999999999996 !!!

 

[Alex_197]

These are normal rounding problems with Double numbers.
A digital number cannot hold the exact value of 503.42, but only the closest one 503.41999999999996.
To display the result use NumberFormat or NumberFormat2.
Try this code:

    Private a, b As Double
    a = 322.19
    b = 181.23
    Log(a + b)

You get: 503.41999999999996 !!!

not this way, but I run a SQL statement to get a sum of 2 rows and if I run this statement in db viewer it works fine and shows me 503.42 but if I run the same SQL from my code it shows 503.41999999999996

 

[agraham]

As @klaus says this is an inevitable side effect of using Doubles that being binary values do not accurately represent all decimal values. If you need decimal precision you can use my BigNumbers library.

BigNumbers library

If you need to do very accurate claculations using decimal numbers then the normal Java numeric types are not appropriate because, being finite length binary numbers, they cannot accurately represent every possible decimal number. The Java libraries contain a BigDecimal class that is optimised...

www.b4x.com

 

[Daestrum]

Is it safe to have the output column name the same as a db field name ?

sum(PaidAmount) As PaidAmount

 

[Alex_197]

Is it safe to have the output column name the same as a db field name ?

sum(PaidAmount) As PaidAmount

this is my sql that produces this error

[CODE lang="b4x" highlight="2"]SELECT
sum(PaidAmount) As PaidAmount,
Max(PaidDate) As PaidDate
FROM tblBilling
where WeekEndDate=?[/CODE]

 

[Alex_197]

As @klaus says this is an inevitable side effect of using Doubles that being binary values do not accurately represent all decimal values. If you need decimal precision you can use my BigNumbers library.

BigNumbers library

If you need to do very accurate claculations using decimal numbers then the normal Java numeric types are not appropriate because, being finite length binary numbers, they cannot accurately represent every possible decimal number. The Java libraries contain a BigDecimal class that is optimised...

www.b4x.com

if I get the value directly from the field by using this code
[CODE lang="b4x" title="D"]log(rs.GetDouble("PaidAmount"))[/CODE]
it gives me the same error

 

[agraham]

Of course it does, it returns a Double value.