This query is returning the record with Min Create time Stamp for the Person Pers_ID when I run it in SQL Developer and the same query is not returning any value from Java JDBC connection.
Can you please help?
select PERS_ID,CODE,BEG_DTE
from PRD_HIST H
where PERS_ID='12345'
and CODE='ABC'
and CRTE_TSTP=(
select MIN(CRTE_TSTP)
from PRD_HIST S
where H.PERS_ID=S.PERS_ID
and PERS_ID='12345'
and EFCT_END_DTE is null
)
Java Code
public static List<String[]> getPersonwithMinCreateTSTP(final String PERS_ID,final String Category,final Connection connection){
final List<String[]> personRecords = new ArrayList<String[]>();
ResultSet resultSet = null;
Statement statement = null;
String PersID=null;
String ReportCode=null;
String effBegDate=null;
try{
statement = connection.createStatement();
final String query="select PERS_ID,CODE,EFCT_BEG_DTE from PRD_HIST H where PERS_ID='"+PERS_ID+"'and CODE='"+Category+"'and CRTE_TSTP=(select MIN(CRTE_TSTP) from PRD_HIST S where H.PERS_ID=S.PERS_ID and PERS_ID='"+PERS_ID+"' and EFCT_END_DTE is null)";
if (!statement.execute(query)) {
//print error
}
resultSet = statement.getResultSet();
while (resultSet.next()) {
PersID=resultSet.getString("PERS_ID");
ReportCode=resultSet.getString("CODE");
effBegDate=resultSet.getString("EFCT_BEG_DTE");
final String[] personDetails={PersID,ReportCode,effBegDate};
personRecords.add(personDetails);
}
} catch (SQLException sqle) {
CTLoggerUtil.logError(sqle.getMessage());
}finally{ // Finally is added to close the connection and resultset
try {
if (resultSet!=null) {
resultSet.close();
}if (statement!=null) {
statement.close();
}
} catch (SQLException e) {
//print error
}
}
return personRecords;
}
