I am trying to write a query to calculate a rank column based upon an aggregate column. The query is an SQLProjection as part of a Hibernate Criteria query. Here is what I have tried:
Code:
String sqlProjection =
"(select count(*) from IPTStatistic stat2 where
max(s.powerRestarts) > max({alias}.powerRestarts)) as rank)";
ProjectionList list = Projections.projectionList();
list.add(Projections.sqlProjection(sqlRankQuery, new String[]{"rank"}, new Type[]{new IntegerType()})));
list.add(Property.forName("managedObjectName").group());
list.add(Projections.max("powerRestarts").as("maxRestarts"));
Criteria crit = hibernateSessionHelper.getSessionFactory().getCurrentSession().createCriteria(IPTStatistic.class);
crit.setProjection(projection);
crit.list();
When I use a non-aggregate column in the SQL projection, the subselect works and I get the expected results, it is only once I introduce the
Code:
max()
that the error occurs.
This throws a fairly non-specific
Code:
org.hibernate.exception.GenericJDBCException
with message
Quote:
Could not execute query
The log shows:
Quote:
WARN logExceptions, SQL Error: -458, SQLState: S1000
ERROR logExceptions, java.lang.NullPointerException java.lang.NullPointerException
I can't pinpoint the problem in the query myself from the above error messages, can anyone give me some pointers on how to correct my query?
**UPDATE:**I am now using the following sqlProjection:
Code:
String sqlProjection = "(select count(*) from " +
"(select name from IPTStatistic s group by s.name " +
" having max(s.powerRestarts) > max({alias}.powerRestarts)) " +
"as r) as rank"
The SQL generated by Hibernate is:
Code:
select (select count(*) from (select iptManagedObjectName from IPTStatistic s group by s.iptManagedObjectName having max(s.powerRestarts) > max(this_.powerRestarts)) as r) as rank, this_.iptManagedObjectName as y1_, from IPTStatistic this_
I am now getting the error:
Quote:
WARN logExceptions, SQL Error: -5581, SQLState: 42581
ERROR logExceptions, unexpected token: SELECT
If I remove
max({alias}.powerRestarts) and replace it with either a constant or
max(s.powerRestarts), then query works (but obviously does not calculate the rank correctly) There seems to be a problem using the
{alias} (which I think is needed), but I'm not sure why.
Thankyou.
Login
FAQ
Search
