multiple resultset

[Devaki Nihar]

Hi
I am using sql server version 2000
Orion 1.5.2
jdk 1.4

I am facing one problem is
In my session bean query to get records ,i am using compute function which returns multiple
records ,It works fine

Code :
--Got connection as conn
--Created Statement as stmt
--initialised resultset and resultmetadata as rs and rsmd

boolean nextResultSet=true;
while(nextResultSet) {
rs = stmt.getResultSet();
rsmd=rs.getMetaData();
int noOfCols=rsmd.getColumnCount();
while(rs.next()){
for(int k=1;k<=noOfCols;k++){
System.out.println(rs.getString(k)); //all columns get as string for display purpose
}
}
nextResultSet=stmt.getMoreResults();
}

suppose my query is something like:

select emp.empid,emp.empname,dept.deptname
from emp,dept
where emp.deptid=dept.deptid
and dept.deptid>3
order by dept.deptid
Copute count(emp.empid) by dept.deptid

It display me all the emp's data whose deptid>3 ordered and grouped by deptid
but it display count of emp's only for the last dept in the group

[edited to add code tags]
[ April 30, 2005: Message edited by: Jeanne Boyarsky ]

[Jeanne Boyarsky]

Devaki,
Welcome to JavaRanch!

The compute clause is meant to present summary information. That is why it is limited to one line.

I don't think there is a way to get all that information in standard SQL. You could do another query (using group by) to calculate the totals. However, it is easier and more efficient to just do the counting in Java. You are returning the department already. If there is more than one department with the same name, you can return the deptId too. Just count the number of records as you loop through the resultset.