This week's giveaway is in the Spring forum.
We're giving away four copies of REST with Spring (video course) and have Eugen Paraschiv on-line!
See this thread for details.
The moose likes Object Relational Mapping and the fly likes Inner Select in HQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of REST with Spring (video course) this week in the Spring forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Inner Select in HQL" Watch "Inner Select in HQL" New topic

Inner Select in HQL

Brent Cromley

Joined: Oct 25, 2004
Posts: 3
I want to represent the following SQL query in HQL.

select time_dim_id,
(select count(*)
from ut_users user2
where user2.user_created_time_dim_id <= time.time_dim_id
) as TotalUsers
from ut_users user1,
ut_time_dim time
where time.time_dim_id = user1.user_created_time_dim_id
group by time_dim_id
order by time_dim_id

Is this possible? I get the following error when I run the query:

Exception: net.sf.hibernate.QueryException: aggregate function expected before ( in SELECT
(select count(*) from UTUserDim user2 where user2.userCreationTimeId = as TotalUsers
from com.plumtree.usagetracker.persist.UTUser user1,
com.plumtree.usagetracker.persist.UTTimeDim time
where user1.userCreationTimeId =
group by
order by

Also, if you know of any work-arounds, that would be great. I am looking to get a running total of users. My report looks like this:

id count(*)
== =========
1 1
2 2
3 5
4 8
5 13
6 27
7 42
8 42
9 50


I agree. Here's the link:
subject: Inner Select in HQL
It's not a secret anymore!