Android Question SQL issue

Discussion in 'Android Questions' started by YAFUHENK, Apr 12, 2015.

  1. YAFUHENK

    YAFUHENK Active Member Licensed User

    Hi,

    I am using the following two sql statements:

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

    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.
     

    Attached Files:

  2. Erel

    Erel Administrator Staff Member Licensed User

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

    Mahares Well Known Member Licensed User

    I changed this line in your table class module from:
    Code:
    L.Text = Values(Col) & CRLF & "∑" & NumberFormat2(Curs.GetDouble2(ColumnWithTotals),1,2,2,True)
    to:
    Code:
    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.
     
    RandomCoder likes this.
  4. YAFUHENK

    YAFUHENK Active Member Licensed 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:
    Code:
    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
    Code:
    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
     

    Attached Files:

  5. Erel

    Erel Administrator Staff Member Licensed User

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

    klaus Expert Licensed 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 !
     
  7. YAFUHENK

    YAFUHENK Active Member Licensed User

    @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.
     
    lemonisdead and RandomCoder like this.
  8. YAFUHENK

    YAFUHENK Active Member Licensed 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
    Code:
    str = Curs.GetDouble2(col)
    into
    Code:
    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
     

    Attached Files:

    Peter Simpson likes this.
  9. klaus

    klaus Expert Licensed 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.
     
  10. YAFUHENK

    YAFUHENK Active Member Licensed 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
     
  11. YAFUHENK

    YAFUHENK Active Member Licensed User

Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice