0

I want to use the resultSet.next() method twice. I mean, I have retrieved 15 rows of employee and their fund (type A, type B) details, within these 15 employees, I want to know how many A funds and B funds are mapped to each employees. 

while (results.next())
{
    int rowCount=results.getInt(7);
    aCount=BigDecimal.ZERO;
    bCount=BigDecimal.ZERO;
    for(int i=1;i<=rowCount;i++)
    {
        if("A".equals(results.getString(4))||"B".equals(results.getString(4)))
        {
             aCount=aCount.add(BigDecimal.ONE);          
        } else
        {
             bCount=bCount.add(BigDecimal.ONE) ;
        }
        results.next();
    }
}

I am using the ROW_NUMBER() method to get the row count and over partitioned by emp id.

Query is running fine, but I am getting below exception:

Exception in thread "main" com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][3.64.82] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null

Can someone help me, how to handle this ?

Thanks you in advance.

Michael Zhavzharov
  • 1,727
  • 13
  • 26
user2462133
  • 64
  • 3
  • 7
  • 2
    Wouldn't it make more sense to create a query that does the counting and you'd just read the 2 values in your Java code? – Kayaman Oct 21 '14 at 07:39
  • 1. Use a while loop that uses `results.next()` as the condition. next returns true if it has moved the cursor to a valid row an false if it has moved the cursor to after all of the rows available. 2. Remove the results.next() from the end of your loop. 3. Fix your logic about incrementing the counters. Currently you'll not increment the bCount if the type if B. Remove the OR and move the second condition (equals B) to an if statement on the else clause. – Dan Temple Oct 21 '14 at 07:40
  • Why do you increment `aCount` for both `A` and `B`? Don't you want to increment `bCount` for `B` rows? Also, show us the query and table structure. Without those we'll just be guessing. – Jim Garrison Oct 21 '14 at 07:47

0 Answers0