Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 2.1.7

hello,
I have a general question.
I want to write a query similar to this.
sql query:
select count(Distinct userid) as users, truckid from unitofwork
where Zoneid='Z101' and truckid is not null
group by truckid

- what are my options ot handle this in hibernate.

1. I tried to do it using criteria...
but I felt its not supported to do this in this release, and can be done
may be in 3.0 and above.

2. tried to use the session.find as follows:
session.find("select uow.ngvTruck, count(uow.ngvUser) "+
"FROM com.voxware.orm.NgvUnitofwork as uow "+
"group by uow.ngvTruck");

this didn't seem to work, I was getting ORA-00979 error saying
its not a group.
my conclusion was, hibernate returns the whole object of ngvUnitof work
and not just the fields I am looking for and its not going to work.

3. so i tried to experiment with createSqlquery
and i am not having any luck or clue why its not working.

I get this error invalid column name
from this option.

session.createSQLQuery(
"select uow.uowstatus as {uow.uowstatus}" +
" FROM ngv_unitofwork {uow}" ,+
" WHERE uow.zoneid = 'Z101' " +
" group by uow.truckid ",
"uow", NgvUnitofwork.class) .list();

appreciate if you could guide me which direction to
follow,
is it possible to do a group by and count query in hibernate..2

an example of a group by..
yes I spent millions of hrs, looking through the forum,
trying various examples, but
doesn't seem to find a way out.
thanks

problems with group by are resolved in hibernate 3 and there isn't backport to 2.x

regards

hi,
does that mean if I have HIbernate 2.x
I shoudln't possibly run any group by queries..
or can I try to do some..?

thanks

you can try, but it work much better in 3.0

hi..

we are using 2.x.
not sure when we will move to 3.0.

so if all I ahve is 2.x.
what is the best way to run count(x) and group by

regards,

hi..
looks like every one else is able to run the count..expect me....

table- A
uowid, truck id, uow status...........20 attirbutes

I would like to count the number of orders in process, group by truck id
like to see
truckid ordersprcess
1 15
2 20

my statement is-
select truckid, count(uowid) from unitofowrk
where uowstatus = 'inprogress'
group by truckid

when I run this in hibernate I get ora-00979 not a group by expression
i was using
session.find(select....);

I see an example in Professional hibernate
chapter 8, to run similar query

List group = session.find(
"select groups.server.name, coutn(groups)
from Group groups group by groups.server.name");

I was not able to see what the groups is defined here as..
is it running 2 queries..

really appreciate if you could please help and explain
what the groups is defined as.
may be I can try my query inthose lines.

I ma using hibernate 2.x.
let me know is it possible to run this query here..
thanks and regards

hi..
thanks

I resolved it.. using session.find

not sure if I can use criteria to do this....???in 2.x
thanks