Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 2.1.8

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

I have a query problem need help.
I have three class named A, B,C. B and C both has a field point to A. and I want to get the A whose B minus Whose C is the largest. and I wirte a SQL like this and it works under Oracle.

select a.id , (select count(*) from B as b where b.yy = a.xx) as yy ,(select count(*) from C as c where c.zz = a.xx ) as zz from A as a order by yy- zz desc.

and it works and i can get the first line as I wanted.

After that, i try to conver it to HQL. But Hibernate give a error message like below, Any one can help me?
--------------------------------------------------------------------------
net.sf.hibernate.QueryException: aggregate function expected before ( in SELECT [ select p.id (select count(*) from com.cobocn.hdms.org.Client c1 where c1.rf11= p.rf19 and rf17='1') from com.cobocn.hdms.org.Client p where p.rf19 is not null order by rec desc ]
at net.sf.hibernate.hql.SelectParser.token(SelectParser.java:92)
at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
at net.sf.hibernate.hql.ClauseParser.end(ClauseParser.java:114)
at net.sf.hibernate.hql.PreprocessingParser.end(PreprocessingParser.java:143)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:30)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138)
at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:295)
at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1572)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1543)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1531)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1523)
at com.cobocn.hdms.org.OrgManager.getRecommender(OrgManager.java:546)
at com.cobocn.hdms.unittest.org.GetRecommenderTest.test0(GetRecommenderTest.java:21)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:421)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:305)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:186)

hi, anybody can help me ? thanks

HQL does not allow subqueries in the select clause.

hibernate team, just curious. how realistic would it be to end all of these situations w/ a tool that could go from SQL to HQL? a sort of AntiDialect ?

Won't really help here since a Dialect is not the thing responsible for interpreting an HQL query ;)

you know what I meant ;)

hi, Steve

I have tried to put the subqueries following the order clause. and it still do not work.
Does that means subqueries can only exists in where clause.
If so, Do you have an idea how to get the record.

thanks .

I make it worked by using native SQL supported by Hibernate as described in Charpter 13.

Thank you every one.