-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 
Author Message
 Post subject: HQL query trouble with UNION
PostPosted: Fri Sep 15, 2006 7:44 am 
Newbie

Joined: Wed Jun 08, 2005 1:18 pm
Posts: 7
Location: China
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.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Sep 15, 2006 9:35 am 
Regular
Regular

Joined: Mon Jun 13, 2005 12:21 pm
Posts: 61
Location: Hannover
To user UNION you must use native SQL statements, cause UNION is not yet supported in HQL.


Top
 Profile  
 
 Post subject: UNION (INTERSECT and MINUS) needed
PostPosted: Fri Sep 29, 2006 5:28 pm 
Newbie

Joined: Fri Sep 29, 2006 5:21 pm
Posts: 1
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.


Top
 Profile  
 
 Post subject:
PostPosted: Sat Sep 30, 2006 6:04 am 
Regular
Regular

Joined: Mon Jun 13, 2005 12:21 pm
Posts: 61
Location: Hannover
There is an JIRA issue for this, you can vote for it:
http://opensource.atlassian.com/project ... 0?page=all


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.