CODEWITHSUNDEEP
javaplsql
Marc Rasmussen
Marc Rasmussen

Reputation: 20555

Pl SQL function makes java application return 0

I have a view in my DBM that looks like this:

SELECT CDN, DN_GRUPPE, AFSLUTTET_AF AS INITIALS, AGENTGRUPPE AS AGENTGROUP, STATUS, CREATED, LAST_UPD , TRUNC(LAST_UPD)- TRUNC(CREATED) AS SOLVED_SECONDS
FROM [email protected] LEFT JOIN KS_DRIFT.SYS_DATE_KS AA ON TO_DATE(TRUNC(LAST_UPD),'YYYY-MM-DD') = AA.THIS_DATE
WHERE TIDSPUNKT > (SYSDATE - 427)

When my Java application tries to get the selected column SOLVED_SECONDS it returns the values as it should.

HOWEVER if I alter the view to the following:

    SELECT CDN, DN_GRUPPE, AFSLUTTET_AF AS INITIALS, AGENTGRUPPE AS AGENTGROUP, STATUS, CREATED, LAST_UPD , CASE WHEN CALCULATE_CALLBACK_DURATION(CREATED,LAST_UPD) is NULL THEN
 CALCULATE_CALLBACK_DURATION(AA.THIS_DATE_OPENING, LAST_UPD) ELSE
CALCULATE_CALLBACK_DURATION(CREATED, LAST_UPD) END AS SOLVED_SECONDS
FROM [email protected] LEFT JOIN KS_DRIFT.SYS_DATE_KS AA ON TO_DATE(TRUNC(LAST_UPD),'YYYY-MM-DD') = AA.THIS_DATE
WHERE TIDSPUNKT > (SYSDATE - 427)

Ie add my own function (CALCULATE_CALLBACK_DURATION) then the result of my Java application is = 0. Not null not NaN just 0.

For example if I use the following code in my Java application:

    public ArrayList<CallQueue> getCallbacks(String startDate, String endDate,
        char c, ArrayList<CallQueue> data) {
    DateTime end = new DateTime(endDate);
    // this method is currently used for testing purposes
    String sql = "SELECT SOLVED_SECONDS FROM KS_DRIFT.NYK_SIEBEL_CALLBACK_AGENT_H_V ";
    System.out.println(sql);
    ResultSet rs = getTable(sql);
    try {
        while (rs.next()) {
            System.out.println(rs.getInt("SOLVED_SECONDS"));
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return null; 
}

private ResultSet getTable(String sql){
    try {
        return Hent_tabel(sql);
    } catch (SQLException e) {e.printStackTrace();}
    return null;
}

public ResultSet Hent_tabel(String Execute) throws SQLException {
    return db.executeQuery(Execute);
}

in the above example the application will print out "0" for each of the rows in the table. However if I look in the table through my DBM the correct number is displayed in the column SOLVED_SECONDS

I feel like I've tried everything and I simply cannot seem to find the problem. if you need additional information please let me know!

Note - in the above example I am able to get any other column in the table with the correct result expect from SOLVED_SECONDS

UPDATE

If I use the Resultset.wasNull(); I get an exception saying no data was read.

Here is a screenshot of the SOLVED_SECONDS in my DBM

enter image description here

And here is the output from Java:

enter image description here

Note that the following output is just a small glimpse the 0's continue for each of the rows in the database.

UPDATE the created view:

The following is the full definition on the view:

Create View KS_DRIFT.NYK_SIEBEL_CALLBACK_AGENT_H_V (CDN, DN_GRUPPE, INITIALS, AGENTGROUP, STATUS, CREATED, LAST_UPD, SOLVED_SECONDS)
As 
  SELECT 
      CDN, DN_GRUPPE, AFSLUTTET_AF AS INITIALS, 
      AGENTGRUPPE AS AGENTGROUP, STATUS, CREATED, LAST_UPD , 
      CASE WHEN CALCULATE_CALLBACK_DURATION(CREATED,LAST_UPD) is NULL 
           THEN  CALCULATE_CALLBACK_DURATION(AA.THIS_DATE_OPENING, LAST_UPD) 
           ELSE CALCULATE_CALLBACK_DURATION(CREATED, LAST_UPD) 
      END AS SOLVED_SECONDS 
  FROM 
      [email protected] 
  LEFT JOIN 
      KS_DRIFT.SYS_DATE_KS AA ON TO_DATE(TRUNC(LAST_UPD),'YYYY-MM-DD') = AA.THIS_DATE 
  WHERE 
      TIDSPUNKT > (SYSDATE - 427)

Upvotes: 0

Views: 125

Answers (1)

user800014
user800014

Reputation:

If it's the same database, I don't see why the result should differ. You're querying in the database directly for the value of your table and in Java for the value in your view. Assure that you do the same query in both and check the results.

What I've seen is the result of Java displaying zero instead of a null value. That's because getInt() return a primitive int.

So, the test I think you should do is:

StringBuilder query = new StringBuilder();
query.append(" SELECT cdn, ");
query.append("       dn_gruppe, ");
query.append("       afsluttet_af AS INITIALS, ");
query.append("       agentgruppe  AS AGENTGROUP, ");
query.append("       status, ");
query.append("       created, ");
query.append("       last_upd, ");
query.append("       CASE ");
query.append("         WHEN Calculate_callback_duration(created, last_upd) IS NULL THEN ");
query.append("         Calculate_callback_duration(AA.this_date_opening, last_upd) ");
query.append("         ELSE Calculate_callback_duration(created, last_upd) ");
query.append("       END          AS SOLVED_SECONDS ");
query.append(" FROM   [email protected] ");
query.append("       left join ks_drift.sys_date_ks AA ");
query.append("              ON To_date(Trunc(last_upd), 'YYYY-MM-DD') = AA.this_date ");
query.append(" WHERE  tidspunkt > ( SYSDATE - 427 ) ");

ResultSet rs = db.executeQuery(query.toString());
while(rs.next()) {
  //check if the field have value or return null. It is needed when your field can return null values from the database
  Integer solvedSeconds = rs.getObject("SOLVED_SECONDS") != null ? rs.getInt("SOLVED_SECONDS") : null;

  System.out.println("Solved seconds: " + (solvedSeconds != null ? solvedSeconds.toString() : ""));

}

And execute this same query in the database. Also, assure that you're using the same user for both tests.

Upvotes: 1

Related Questions