I have two queries: (one is computing sum of records while the other is selecting all the records)
Query 1:
select sum(tb_nominal) from tb_sec_tran where tb_account_id = 112 and tb_security_id = 234
and trunc(tb_execution_time) < trunc(to_date('31-12-2017','dd-mm-yyyy');
Query 2: select tb_nominal from tb_sec_tran where tb_account_id = 112 and tb_security_id =
234 and trunc(tb_execution_time) < trunc(to_date('31-12-2017','dd-mm-yyyy');
What lies in database.
tb_nominal | tb_execution_time
520 | 08-MAR-17
486 | 04-JAN-18
520 | 01-JAN-18
-520 | 31-DEC-17
Case 1: I ran these two queries on the database(sql developer). For the first query, I am getting 520.0 as the output. For the second query, I am getting 1 record in output with tb_nominal=520.0.
Case 2: on java platform, I have implemented the below code:
PreparedStatement pstmt = null;
ResultSet rs = null;
Double sum = null;
StringBuffer sql = new StringBuffer(" select sum(tb_nominal) from tb_sec_tran where
tb_account_id = ? and tb_security_id = ? and trunc(tb_execution_time) < trunc(?) ");
pstmt = m_Connection.prepareStatement(sql.toString());
pstmt.setLong(1, spekuVO.getContent().getAccountId());
pstmt.setLong(2, spekuVO.getContent().getInstrumentId());
pstmt.setDate(3, toDate.getSQLDatum());
rs = pstmt.executeQuery();
if (rs.next()) {
sum = rs.getDouble(1);
}
Problem: So when I run the code, instead of 520.0 nominal value, I am getting the sum as 0.0 which is different from the what was returned on the database. Similarly if I make a change in the sql variable by deleting the sum word to make it similar to the query 2, I get two records as in the image which i have attached.
I am completely baffled by the strange behavior. Is there any change made in the processing of JAVA 8 compared to JAVA 6 related to this? Kindly suggest me if any reason exist for this behavior.
No, Java doesn't magically give out wrong results.
Your Java query doesn't contain the to_date part that you're using in your other query. Two different queries, two different results.