Android Question SQL issue

[Yafuhenk]

Hi,

I am using the following two sql statements:

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:

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.

 

[Erel]

How large is the difference? Have you tried to use doubles instead of floats?

 

[Mahares]

I changed this line in your table class module from:

L.Text = Values(Col) & CRLF & "∑" & NumberFormat2(Curs.GetDouble2(ColumnWithTotals),1,2,2,True)

to:

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.

 

[Yafuhenk]

@ 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:

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

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

 

[Erel]

I'm not really familiar with flexible table class. You should go over the code and see how it calculates the sum.

 

[klaus]

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 !

 

[Yafuhenk]

@klaus, there is no need to say sorry. I am very happy with your input !.
I will check in detail and will place my findings here.

 

[Yafuhenk]

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

str = Curs.GetDouble2(col)

into

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

 

[klaus]

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.

 

[Yafuhenk]

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

 

[Yafuhenk]

Statusline of flexible tab in tabhost has already been solved
https://www.b4x.com/android/forum/threads/flexible-table-on-tabhost.52777/