wood burning stoves
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 OCA Java SE 8 Programmer I Study Guide this week in the OCAJP 8 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 time.id,
(select count(*) from UTUserDim user2 where user2.userCreationTimeId = time.id) as TotalUsers
from com.plumtree.usagetracker.persist.UTUser user1,
com.plumtree.usagetracker.persist.UTTimeDim time
where user1.userCreationTimeId = time.id
group by time.id
order by time.id

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: http://aspose.com/file-tools
subject: Inner Select in HQL
It's not a secret anymore!