Somebody can tell me what should i do to avoid this error?
Thanks
Phil

Caused by: org.hibernate.hql.ast.QuerySyntaxError: unexpected token: ( near line 1, column 1
[(select C.idcity as ALIAS,C.city,count(P.idprofile) from com.project.model.bean.City as C, com.project.model.bean.Member as M, com.project.model.bean.Profile as P where P.topic_idtopic=1 and M.idmember=P.member_idmember and M.default_idcity=C.idcity group by idcity) UNION (select C.idcity,C.city,count(E.idevent) from com.project.model.bean.City as C, com.project.model.bean.Event as E where E.topic_idtopic=1 and C.idcity=E.city_idcity group by C.idcity) order by ALIAS]

Basically my query is like this
(select idcity as ALIAS....from City1) UNION (select idcity ... from City2) order by ALIAS
tested directly in SQL before. Did a try with hql because this is the one i'm used to work with.

To user UNION you must use native SQL statements, cause UNION is not yet supported in HQL.

I have been working around the union/intersect problem by wrapping the HSQL statements separately and using the "IN" operator.

the problem here is that each DB has its limits on how many elements can be used for the IN set (oracle has 10000, sqlsvr has 2000). i've run into this limit already so it would be great to have HQL support this.

There is an JIRA issue for this, you can vote for it:
http://opensource.atlassian.com/project ... 0?page=all