wood burning stoves 2.0*
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 Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Inner Select in HQL" Watch "Inner Select in HQL" New topic
Author

Inner Select in HQL

Brent Cromley
Greenhorn

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


Thanks,

Brent
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Inner Select in HQL
 
Similar Threads
retrieve data from huge result set
SQL to Hibernate HQL conversion error
How many connections
Deleting duplicate rows using sql query
Need help with a query (HQL)