Java returning different result from SQL when running identical query

Question

I have an SQL statement written in Java (netbeans/uncanaccess) that is a fairly simple select statement with a few IIF and SUM from one table in my database.

When I run the SQL statement in Access it returns the correct results, however when I have tried to run it in Java, it returns results that are similar, but not exactly the same.

Java Code

public int getActualMHDetails(String strNumber, String strYear, String strPeriod){
    String strSQLString = null;
    System.out.println("Getting cost details for: " + strNumber);
    try{

strSQLString = "SELECT tblExportCost.ProjDef, tblExportCost.Year,\n"
                        + "Sum(IIf([Per]=1,[Val/ObjCur],0)) AS Jan, Sum(IIf([Per]=2,[Val/ObjCur],0)) AS Feb, Sum(IIf([Per]=3,[Val/ObjCur],0)) AS Mar,\n"
                        + "Sum(IIf([Per]=4,[Val/ObjCur],0)) AS Apr, Sum(IIf([Per]=5,[Val/ObjCur],0)) AS May, Sum(IIf([Per]=6,[Val/ObjCur],0)) AS Jun,\n"
                        + "Sum(IIf([Per]=7,[Val/ObjCur],0)) AS Jul, Sum(IIf([Per]=8,[Val/ObjCur],0)) AS Aug, Sum(IIf([Per]=9,[Val/ObjCur],0)) AS Sep,\n"
                        + "Sum(IIf([Per]=10,[Val/ObjCur],0)) AS Oct, Sum(IIf([Per]=11,[Val/ObjCur],0)) AS Nov, Sum(IIf([Per]=12,[Val/ObjCur],0)) AS Dec\n" 
                        + "FROM tblExportCost\n" 
                        + "GROUP BY tblExportCost.ProjDef, tblExportCost.Year, tblExportCost.Year\n" 
                        + "HAVING (((tblExportCost.Year)= '" + strYear + "') AND ((tblExportCost.ProjDef)= 'T3415" + strNumber + "'))";

//SETTING PREPARED STATEMENT
        PreparedStatement preStatement = con.prepareStatement(strSQLString, ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

        ResultSet rs = preStatement.executeQuery();

        rs.next();

        //IF CHECKS TO MAKE SURE RECORDS
        if(rs.getRow()==0){
            rs.close();      
            preStatement.close();
            return 3;
        }        

        strTest = rs.getString("Jan");

        System.out.println("Test Value: " + strTest); 

        intAMHCJan = rs.getInt("Jan");
        intAMHCFeb = rs.getInt("Feb");
        intAMHCMar = rs.getInt("Mar");
        intAMHCApr = rs.getInt("Apr");
        intAMHCMay = rs.getInt("May");
        intAMHCJun = rs.getInt("Jun");
        intAMHCJul = rs.getInt("Jul");
        intAMHCAug = rs.getInt("Aug");
        intAMHCSep = rs.getInt("Sep");
        intAMHCOct = rs.getInt("Oct");
        intAMHCNov = rs.getInt("Nov");
        intAMHCDec = rs.getInt("Dec");

        //CLOSES CONNECTIONS
        System.out.println("Database query successful; closing connections");
        rs.close();
        preStatement.close();            

        return 1;

    }catch(Exception ex){
        ex.printStackTrace();
        strEXMessage=ex.getMessage();
        return 2;
    }
}

Access Code

SELECT tblExportCost.ProjDef, tblExportCost.Year,
Sum(IIf([Per]=1,[Val/ObjCur],0)) AS Jan, Sum(IIf([Per]=2,[Val/ObjCur],0)) AS Feb, Sum(IIf([Per]=3,[Val/ObjCur],0)) AS Mar,
Sum(IIf([Per]=4,[Val/ObjCur],0)) AS Apr, Sum(IIf([Per]=5,[Val/ObjCur],0)) AS May, Sum(IIf([Per]=6,[Val/ObjCur],0)) AS Jun,
Sum(IIf([Per]=7,[Val/ObjCur],0)) AS Jul, Sum(IIf([Per]=8,[Val/ObjCur],0)) AS Aug, Sum(IIf([Per]=9,[Val/ObjCur],0)) AS Sep,
Sum(IIf([Per]=10,[Val/ObjCur],0)) AS Oct, Sum(IIf([Per]=11,[Val/ObjCur],0)) AS Nov, Sum(IIf([Per]=12,[Val/ObjCur],0)) AS Dec
FROM tblExportCost
GROUP BY tblExportCost.ProjDef, tblExportCost.Year, tblExportCost.Year
HAVING (((tblExportCost.Year)= 2016) AND ((tblExportCost.ProjDef)= 'T34151234'))

I have even tried to save the Access query and simply use

strSQLString = "SELECT * FROM qryTestJava";

but this also returns the same incorrect results.

The Results

SQL

ProjDef     Year   Jan      Feb               Mar          Apr        May   
T34151234   2016  22358.1  18742.9  3443.33000000001    10251.03    12706.78

Java

ProjDef     Year   Jan      Feb         Mar        Apr        May   
T34151234   2016  22,329  18,714       3,420      10,226    12,684

I did a bit of digging and found a similar problem with the rounding and ucanaccess Here, but it was reportedly fixed in an earlier version.

My current version of ucanaccess is 2.0.9.3

Can you try using the latest version of UCanAccess (currently 3.0.5) instead? — Gord Thompson, May 31 '16 at 14:48
Also, `3443.33000000001` looks like a floating-point value. Can you use Access to open [tblExportCost] in Design View and confirm the column types for [Val] and [ObjCur]? (Single? Double? Currency? ...?) — Gord Thompson, May 31 '16 at 15:33
Probable need to replace the HAVING by a WHERE clause? HAVING is mostly when you use it to limit on functions, but the SQL tries to limit on just some fields. Also, no need to group twice on the same field. — LukStorms, May 31 '16 at 15:39
As Gord said, it looks like both [Val] and [ObjCur] are integer. If so, you just have to do cdbl( [Val] )/ [ObjCur] instead of [Val] )/ [ObjCur] . If not, please let us know the types of these columns. — jamadei, Jun 01 '16 at 08:50
@GordThompson The field size is double and Format is General Number, will try to upgrade to newest version of ucanaccess today and report back. — ppw, Jun 01 '16 at 10:31
@jamadei correct. I believe surrounding the column name with [ ] in the code makes sure no other symbols are confused with calculations? (I think that's correct anyway) — ppw, Jun 01 '16 at 14:47
*"I believe surrounding the column name with [ ] in the code makes sure no other symbols are confused with calculations?"* - Yes, that's right. Sorry for not noticing that earlier. — Gord Thompson, Jun 01 '16 at 15:12

Gord Thompson · Accepted Answer · 2016-06-02T00:05:19.187

I was able to reproduce your issue under UCanAccess 3.0.5. The IIf() function appears to truncate double values to their integer value when invoked as

SELECT IIf([Per]=1,[Val/ObjCur],0) AS ...

while the entire double value is correctly returned when IIf() is called with 0.0 as the numeric literal value, i.e.,

SELECT IIf([Per]=1,[Val/ObjCur],0.0) AS ...

So you should be able to retrieve the correct SUMs by using

strSQLString = "SELECT tblExportCost.ProjDef, tblExportCost.Year,\n"
                        + "Sum(IIf([Per]=1,[Val/ObjCur],0.0)) AS Jan, Sum(IIf([Per]=2,[Val/ObjCur],0.0)) AS Feb, Sum(IIf([Per]=3,[Val/ObjCur],0.0)) AS Mar,\n"
                        + "Sum(IIf([Per]=4,[Val/ObjCur],0.0)) AS Apr, Sum(IIf([Per]=5,[Val/ObjCur],0.0)) AS May, Sum(IIf([Per]=6,[Val/ObjCur],0.0)) AS Jun,\n"
                        + "Sum(IIf([Per]=7,[Val/ObjCur],0.0)) AS Jul, Sum(IIf([Per]=8,[Val/ObjCur],0.0)) AS Aug, Sum(IIf([Per]=9,[Val/ObjCur],0.0)) AS Sep,\n"
                        + "Sum(IIf([Per]=10,[Val/ObjCur],0.0)) AS Oct, Sum(IIf([Per]=11,[Val/ObjCur],0.0)) AS Nov, Sum(IIf([Per]=12,[Val/ObjCur],0.0)) AS Dec\n" 
                        + "FROM tblExportCost\n" 
                        + "GROUP BY tblExportCost.ProjDef, tblExportCost.Year\n" 
                        + "HAVING (((tblExportCost.Year)= '" + strYear + "') AND ((tblExportCost.ProjDef)= 'T3415" + strNumber + "'))";

works perfectly, many thanks as always Gord for your answer and clear explanation! — ppw, Jun 02 '16 at 08:51

Java returning different result from SQL when running identical query

1 Answers1