Android Question SQL issue

Yafuhenk

Active Member
Licensed User
Longtime User
Hi,

I am using the following two sql statements:

B4X:
Result(0) = "SELECT SalesManagerCode AS 'Sales Manager Number', SalesManagerName AS 'Sales Manager Name', IFNULL(SUM(TurnoverEuro),0) AS 'Turnover', IFNULL(SUM(ScheduledOnHandEuro),0) AS 'Scheduled on hand' FROM " & Main.TABLENAME0 & " WHERE FiscalYear = 'V9/" & Main.fy & "' GROUP BY SalesManagerCode, SalesManagerName ORDER BY SalesManagerName"
    Result(1) = "SELECT IFNULL(SUM(TurnoverEuro),0) AS 'Turnover', IFNULL(SUM(ScheduledOnHandEuro),0) AS 'Scheduled on hand' FROM " & Main.TABLENAME0 & " WHERE FiscalYear = 'V9/" & Main.fy & "'"

According to me the sum of Result(0) should be equal to Result(1), however it isn't. Most of the time there is a few Euro difference. What do I wrong?

The rounding is done in the flexible table module with this code:

B4X:
lbls(I).Text = NumberFormat2(values(I),1,2,2,True)

Attached a demo program which creates a dummy database of 5000 rows so that you can create an overview yourself.

Also a print screen where you can see that the sum of the turnover per salesmanager is not equal to the total shown the header.
 

Attachments

  • Test_DB.zip
    43.8 KB · Views: 316
  • Screenshot_2015-04-12-14-13-57.png
    Screenshot_2015-04-12-14-13-57.png
    117.2 KB · Views: 320

Mahares

Expert
Licensed User
Longtime User
I changed this line in your table class module from:
B4X:
L.Text = Values(Col) & CRLF & "∑" & NumberFormat2(Curs.GetDouble2(ColumnWithTotals),1,2,2,True)
to:
B4X:
L.Text = Values(Col) & CRLF & "∑" & NumberFormat2(Curs.GetString2(ColumnWithTotals),1,2,2,True)
which yielded the same result for me: Result(0) is the same as Result(1). I hope that is what you are looking for.
 
Upvote 0

Yafuhenk

Active Member
Licensed User
Longtime User
@ Mahares,
If I change the code as suggested by you the turnover figures are the same but now the scheduled on hand figures are not the same anymore. At least in the database I have. Further more I can mention that the totals exactly fit to the .NET software I wrote and checked with Excel. I am therefore sure that the totals are correct.

@ Erel.
I changed in the creating database sub real into double and float into double.
Same problem. I think is has to do with the flexible table.
Where I get the header totals directly from the database via a query with correct figures; in the flexible class module show mostly incorrect. I saw that the values of the rows are stored as strings. Can that be the reason?

So this works
The the SetHeader Sub of the Flexible Table Class:
B4X:
If RightAlignment = True Then
                L.Text = Values(Col) & CRLF & "∑" & NumberFormat2(Curs.GetDouble2(ColumnWithTotals),1,2,2,True)
                L.Gravity = Gravity.RIGHT
                ColumnWithTotals = ColumnWithTotals + 1
            Else

This shows mostly wrong figures (or should I say inaccurate figures)
The Showrow Sub of the Flexible Table Class
B4X:
If RightAlignment = True Then
                    Dim ValueDouble As Double = Values(I)
                    lbls(I).Text = NumberFormat2(ValueDouble,1,2,2,True)
                    lbls(I).Gravity = Gravity.RIGHT
                Else
                    lbls(I).Text = values(I)
                    lbls(I).Gravity = Gravity.LEFT
                End If

I recognized that the sub btn_creatdb_click was empty in the example. This has been changed.
After creation of the database a messagebox shows the correct values as they should appear in the flexible table (but unfortunately they still don't)

Best regards

Henk
 

Attachments

  • TestDB.zip
    43.9 KB · Views: 309
Upvote 0

klaus

Expert
Licensed User
Longtime User
Sorry for answering to your problems only today, but I was on travel for a week and came back today.
The problem of the wrong figures has nothing to do with the Felxible Table.
The problem is that you get the wrong values from the SQL query !
The sum values you show in the headers come directly from your query, there is nothing modified by the class code.
Put a Log in the SetHeader routine showing directly the values from the cursor and you'll see that these are wrong.
You need to check your query and database !
 
Upvote 0

Yafuhenk

Active Member
Licensed User
Longtime User
Hi Klaus, I checked the imported excel file the origional database and the sql. They are fine.
The problem is in the flexible table class

In the Public Sub LoadSQLiteDB(SQLite AsSQL, QueryAsString, AutomaticWidths AsBoolean)
you should change
B4X:
str = Curs.GetDouble2(col)
into
B4X:
If IsNumber(Curs.GetString2(col)) = True Then
                str = Curs.GetDouble2(col)
            Else
                str = Curs.GetString2(col)
            End If

See the result in the print screens below

Best regards

Henk
 

Attachments

  • Screenshot_2015-04-13-21-28-22.png
    Screenshot_2015-04-13-21-28-22.png
    124.4 KB · Views: 325
  • Screenshot_2015-04-13-21-31-00.png
    Screenshot_2015-04-13-21-31-00.png
    126.5 KB · Views: 320
Upvote 0

klaus

Expert
Licensed User
Longtime User
I see it too now.
The problem is with big numbers.
Example:
GetDouble > 1497823.0
GetString > 1.49782e+06
In your code you convert the String back to Double which gives 1497820.
I was not aware that GetString converted big numbers to scientific notation with only 6 digits.
I will amend the Table class soon.
 
Upvote 0

Yafuhenk

Active Member
Licensed User
Longtime User
Thanks Klaus. The flexible table class is getting better and better.
I still have one issue for which I will start a new topic.
When usinging the flex table in a tabhost I can't see the status line.
I would appreciate if you get have a look at that post well. (I need a few minutes to write the post :)

Henk
 
Upvote 0
Top