strange or perhaps not so strange double variable behavior

[mc73]

Almost accidentally, while summing two double variables (with just 1 decimal digit), I received in a label, the sum containing many decimal digits. Now I know that doubles are just approximations, yielding many decimal places occasionally, so I tried formating the two doubles using numberFormat(1,2) before summing. No difference!
I submitted these values in a db. The fields under concern, are defined as REAL. If you have a look (db attached) and query

select openorders from pos2

you can see the values entered. Now, if you execute the query

select sum(openorders) from pos2

you will see the extra decimal digits! It's annoying, since I cannot exactly understand why numberFormat is not truncating these numbers. To not mention problems causing in a reports' module (the module cannot even recognize some submitted data).
Attached you can find both the database and a screenshot of the two queries.

Any ideas?

 

[agraham]

I tried formating the two doubles using numberFormat(1,2) before summing.

NumberFormat produces a string intended for display. Use it after doing all the maths not before.

 

[mc73]

NumberFormat produces a string intended for display. Use it after doing all the maths not before.

Means that when I write the numberFormatted string to the db, it will not really be the number formatted but the original?

 

[mc73]

Here's some code

itemsValue(insideOrderCounter)=NumberFormat(itemPrice*itemsQuant2(insideOrderCounter),1,2)

 

[agraham]

I don't know exactly how SQLite stores its data, but if you are saving it as a double then Basic4android will convert the string back to a double before passing it to SQLite. The saved data is not your problem however, it is the inherent imprecision in representing decimal numbers in a binary format. Just use NumberFormat to obtain the precision you want when you display the data.

 

[mc73]

Andrew, I already present data using numberFormat for viewing puproses. The main problem is that in some reports, I have to sum these values. And there is the real concern, since Summing refuses to sum these particular values and spits out an empty string! This is just so annoying! I have to get rid of that, perhaps I will alter the table fields to string type, who knows...

 

[Mahares]

select sum(openorders) from pos2

@mc73: In your SQLite query statement you can use either of the two below to set the preferred number of decimals:

Select Round(Sum(openorders),2) from pos2  'to round to 2 decimals

or

select Sum(Round(openorders,2)) from pos2

Please do not use Round2 as it is for B4A.

 

[mc73]

My friend Mahares,

thank you, I already engage such functions in my queries. The problem is not how I handle viewing data, the problem is how consistent it it to write such data in sqlite. As already said, I occasionally experience missing fields due to the write process. I will get back as soon as I find what is really going wrong and of course inform you on this

 

[Mahares]

I have had similar problems. I started storing data in SQLite tables as TEXT data type even for numbers. I generally avoid REAL and INTEGER (except for a PRIMARY KEY). And you can still validate the data for numbers before INSERTS and UPDATES. Yo can also prevent unwanted 0s because when you INSERT an empty string in a REAL or INTEGER field it inserts 0 even if you wanted it to be blank. I am sure you know that too.
Your friend Mahares

 

[timo]

Have a look at here

 

[mc73]

So, here's a small example in b4a:

lb.Initialize ("")
    Dim a As Double, b As Double ,c As Double 
    Dim s As Double
    a=5.1:b=4.2:c=4.8
    s=a+b+c
    lb.Text =s
    Activity.AddView (lb,0,0,50%x,10%y)

This thing returns the 'unexpected' 14.10-----------1 value.

I guess I have to perform a numberFormat every time I'm doing some float calcs from now on. Seems like sqlite stores what is really there in the first place.
Another alternative is to start using cents and declare integer variables. I used this thing in the past for cash registers, I guess I can easily do it also for these calculations.

 

[agraham]

This thing returns the 'unexpected' 14.10-----------1 value.

It is not unexpected. It is an expected outcome when you are using floats or doubles. That is why NumberFormat is available.

 

[mc73]

I used the '.
Anyway, running the very same on c or vb, I get the correct result and I think this explains the 'unexpected', while vb still has a formatNumber function.

 

[klaus]

I had similar problems with VB too some years ago.
In some languages is the calculations are made with about 17 digits with the same result as you see but displayed rounded to 15 digits so you dont see the last ones.

Best regards.

 

[mc73]

Thank you for this being clarified Klaus. In fact I remember some problems and while dealing with floats I was always using longs (doubles multiplied by the 10^N-significant-digits). Anyway, I'll alter my code and hopefully everything will be just fine again