CODEWITHSUNDEEP
javaoracle-databaseselectresultset
tjlang
tjlang

Reputation: 27

How to have multiple Select Statements in a Result Set in Java

The problem I am running into currently is i want to select a column and sum the values. Then I want to sum up another column in the same table. The object is to take these two summed up columns and subtract them to get an output. My use for this is taking vacation times. Every week the employee gains hours of vacation. Then when they are used they are in a different column. I need to come up with the total of vacation left they can use. This is what I have: 

try
    {
         //Getting Information FROM EMPLOYEE_TIME_LOG for Vacation
            Statement vacationLogstmt = dbConn.createStatement();
            ResultSet vacationLogSet = vacationLogstmt.executeQuery(
                         "(SELECT SUM(vacation_gained) FROM employee_time_log WHERE employee_id_number = " +userInputIdNumber + "), " +
                         "(SELECT SUM(vacation_used) FROM employee_time_log WHERE employee_id_number = " +userInputIdNumber + "), " +
                     "GROUP BY vacation_gained, vacation_used");

           vacationLogSet.next();
           String strVacationEarned = vacationLogSet.getString(1);
           String strVacationUsed = vacationLogSet.getString(2);
           Double vacationEarned = Double.parseDouble(strVacationEarned);
           Double vacationUsed = Double.parseDouble(strVacationUsed);
           Double totalVacation = (vacationEarned - vacationUsed); 
           String strTotalVacation =Double.toString(totalVacation);
           txtVacationTotal.setText(strTotalVacation);
    }
    catch (SQLException e)
    {
          //throw new JboException(e);
            System.err.println(e);
            String connectionError = "Problem with the database, please contact MIS.";
            JOptionPane.showMessageDialog(new JFrame(), connectionError, "Database Error",JOptionPane.ERROR_MESSAGE);
            txtUserSignIn.setText("");
            txtUserSignIn.requestFocus();      
    }

I am getting an error: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

Can this even be done in one result set? or do i need to just make separate result sets to do this.

Upvotes: 0

Views: 768

Answers (1)

matt.early
matt.early

Reputation: 225

There are a couple of problems with your sql. First, you can not have multiple selects separated by commas. Instead, you should have one select with multiple columns. Second, you should not group by vacation_gained, vacation_used because you are using these fields in aggregate functions. The sql should be as follows:

"(SELECT SUM(vacation_gained), SUM(vacation_used) FROM employee_time_log WHERE employee_id_number = " + userInputIdNumber + ")";

Alternatively, if you are just interested in the vacation time left, you can do the math as part of the query as well:

"(SELECT SUM(vacation_gained) - SUM(vacation_used) as total_vacation FROM employee_time_log WHERE employee_id_number = " + userInputIdNumber + ")";

Upvotes: 1

Related Questions