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
– Gord Thompson, Commented May 31, 2016 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
– Gord Thompson, Commented May 31, 2016 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
– LukStorms, Commented May 31, 2016 at 15:39
@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
– ppw, Commented Jun 1, 2016 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
– Gord Thompson, Commented Jun 1, 2016 at 15:12

Gord Thompson · Accepted Answer · 2016-06-02 00:05:19Z

4

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 + "'))";

edited Jun 2, 2016 at 0:05

answered Jun 1, 2016 at 23:44

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ppw Over a year ago

works perfectly, many thanks as always Gord for your answer and clear explanation!

Collectives™ on Stack Overflow

Java returning different result from SQL when running identical query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related