Pl SQL function makes java application return 0

Question

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)

Could you please update your Java code to the actual code you're executing? Your current code wouldn't compile... — Lukas Eder
– Lukas Eder, Commented Apr 23, 2013 at 12:32
Are you sure that you're checking your second query in the database? Maybe the view is returning zero, probably because of your case. — user800014
– user800014, Commented Apr 23, 2013 at 12:33
@MarcRasmussen: I don't want to be too picky, but to exclude that you're doing something wrong on the JDBC side, I really need to see the actual code. Your posted code won't compile (.next without parentheses, Resultset with lower-case s..., missing closing parenthesis on the println() call). Please post the code that you have successfully executed, which is giving you the unwanted zeroes — Lukas Eder
– Lukas Eder, Commented Apr 23, 2013 at 12:36
@SérgioMichels the view is displaying the right number in the database so i assume that the query is executed correctly — Marc Rasmussen
– Marc Rasmussen, Commented Apr 23, 2013 at 12:37

score 1 · Accepted Answer · 2013-04-23 13:13:42Z

1

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.

edited Apr 23, 2013 at 13:13

answered Apr 23, 2013 at 13:06

user800014

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

6 Comments

Marc Rasmussen Over a year ago

When i run the above code i get a nullpointer exepection on : System.out.println("Solved seconds: " + solvedSeconds.toString());

Marc Rasmussen Over a year ago

type mismatch cannot convert from integer to boolean if i change it to: System.out.println("Solved seconds: " + (solvedSeconds != null ? solvedSeconds.toString() : "")); Then i get the following output: Solved seconds: Solved seconds: Solved seconds: Solved seconds: Solved seconds: Solved seconds: Solved seconds: Solved seconds: Solved seconds:

user800014 Over a year ago

Sorry, I'm Groovy addicted :-) Check again.

user800014 Over a year ago

Then your case returns null. Run the same query in the database and see the result...

Marc Rasmussen Over a year ago

if i run that in my database i get a result and SOLVED_SECONDS is displayed correctly

|

Collectives™ on Stack Overflow

Pl SQL function makes java application return 0

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related