Thanks for the reply! I get no errors. The "or (stat_cd = 'VA' and updt_dts < current_date - 30/1440)" part of the statement is working differently that it would when run in Toad because it selects rows with stat_cd = 'VA' regardless of the date. When I run the query in Toad, it properly selects rows of stat_cd = 'VA' using the date. I've tried it with a PreparedStatement and a "regular" Statement but get the same results. I even tried changing the 30/1440 to .0208 but that was just on a crazy whim. I need to play around some more but it seems quite irregular that *any* query would work differently, unless
jdbc is not passing the *text* to oracle and letting oracle interpret it but rather interpreting it first and then passing it to oracle. In case you're interested, here's the log entry that shows the value of the SQL statement
string and a snippet of code:
RADAR:2010.03.10.09.37.48:INFO:com.mckesson.radar.spf.SpfDAO:searchHeader:User Id=xxxxxxx, SQL=SELECT crte_dts, crte_usr_id, dm_3pty_ref_num, dm_id, dm_ref_id, err_cd_list, image_id, note_descr, source_id, spf_staging_id, splr_nam, stat_cd, updt_dts, updt_usr_id, wrtoff_amt FROM s_spf_staging_hdr WHERE ROWNUM <= 501 and (stat_cd in ('ER') or (stat_cd = 'VA' and updt_dts < current_date - 30/1440)) order by spf_staging_id ASC
String sqlStmt = sb.toString();
radarCommonLogger.log(Level.INFO, "User Id=" + userId + ", SQL=" + sqlStmt);
Connection conn = null;
ResultSet rs = null;
PreparedStatement ps = null;
try {
DataSource ds = RadarUtil.getDataSource();
conn = ds.getConnection();
ps = conn.prepareStatement(sqlStmt);
rs = ps.executeQuery();
while (rs.next()) {
spfListForm.addToSpfHeaderBeanList(new SpfHeaderBean(rs));
}
} finally {
RadarUtil.closeJdbcObj(conn, ps, rs);
}