Hibernate version: 3.0.4
Name and version of the database you are using: Oracle 10g


I hope by answering other questions I get some good forum karma. :) I am trying to do: (pseudo HQL query, actual HQL below)



but get an error (listed below). In Oracle I can do that type of query. (A count of the num of results of a subselect.) Do i need to use Native SQL? My temp solution is to return the resulting list of the inner query and then do a size(), but this would be problematic for large result sets obviously.

Stack trace below, thank you for any thoughts!

Full stack trace of any exception that occurs:
16:16:41,687 DEBUG QueryTranslatorImpl:207 - parse() - HQL: select count(*) from (select count(*) FROM tv.current.base.model.Viewing viewing join viewing.user viewer join viewing.asset asset join asset.producer producer WHERE producer.id = ? group by viewer.id, asset.id)
16:16:41,687 ERROR PARSER:35 - *** ERROR: line 1:22: unexpected token: (
16:16:41,703 DEBUG ErrorCounter:28 - line 1:22: unexpected token: (
line 1:22: unexpected token: (
at org.hibernate.hql.antlr.HqlBaseParser.fromRange(HqlBaseParser.java:1178)
at org.hibernate.hql.antlr.HqlBaseParser.fromClause(HqlBaseParser.java:1023)
at org.hibernate.hql.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:745)
at org.hibernate.hql.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:599)
at org.hibernate.hql.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:260)
at org.hibernate.hql.antlr.HqlBaseParser.statement(HqlBaseParser.java:150)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:209)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:127)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:425)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:884)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:834)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)


The generated SQL (show_sql=true):
sql fails, HQL = select count(*) from (select count(*) FROM tv.current.base.model.Viewing viewing join viewing.user viewer join viewing.asset asset join asset.producer producer WHERE producer.id = ? group by viewer.id, asset.id)[/b]

HQL does not support from-clause subselects