hibernate3.0

Mapping documents:

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



19-Sep 09:27:13 ERROR QueueItemHomeDuplicate
unexpected token: minus near line 1, column 369 [from QueueItem queueItem where queueItem.itemType = ? and queueItem.queue.id = ? and queueItem.draftApplication.draftApplicationStatus.id <> ? and queueItem.draftApplication.draftApplicationStatus.id <> ? and queueItem.transaction.transactionStatus.id <> ? and queueItem.transaction.transactionStatus.id <> ? minus select transaction.id from queueItem where queueItem.itemType=? AND queueItem.queue.id=?]
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: minus near line 1, column 369 [from QueueItem queueItem where queueItem.itemType = ? and queueItem.queue.id = ? and queueItem.draftApplication.draftApplicationStatus.id <> ? and queueItem.draftApplication.draftApplicationStatus.id <> ? and queueItem.transaction.transactionStatus.id <> ? and queueItem.transaction.transactionStatus.id <> ? minus select transaction.id from queueItem where queueItem.itemType=? AND queueItem.queue.id=?]
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31)
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:24)
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:258)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:157)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
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:585)
at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:301)
at $Proxy0.createQuery(Unknown Source)
at QueueItemHomeDuplicate.retrieveInstancesForTransAndSignItems(QueueItemHomeDuplicate.java:57)
at QueueItemHomeDuplicate.findByQueue_Type_Status(QueueItemHomeDuplicate.java:29)
at TestHibernate.retrieveInTrayByQueueId(TestHibernate.java:48)
at TestHibernate.main(TestHibernate.java:97)

at QueueItemHomeDuplicate.findByQueue_Type_Status(QueueItemHomeDuplicate.java:39)
at TestHibernate.retrieveInTrayByQueueId(TestHibernate.java:48)
at TestHibernate.main(TestHibernate.java:97)
Caused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: minus near line 1, column 369 [from QueueItem queueItem where queueItem.itemType = ? and queueItem.queue.id = ? and queueItem.draftApplication.draftApplicationStatus.id <> ? and queueItem.draftApplication.draftApplicationStatus.id <> ? and queueItem.transaction.transactionStatus.id <> ? and queueItem.transaction.transactionStatus.id <> ? minus select transaction.id from queueItem where queueItem.itemType=? AND queueItem.queue.id=?]
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31)
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:24)
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:258)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:157)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
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:585)
at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:301)
at $Proxy0.createQuery(Unknown Source)
at QueueItemHomeDuplicate.retrieveInstancesForTransAndSignItems(QueueItemHomeDuplicate.java:57)
at QueueItemHomeDuplicate.findByQueue_Type_Status(QueueItemHomeDuplicate.java:29)
... 2 more




Oracle9ib]

[b] from QueueItem queueItem where queueItem.itemType = ? and queueItem.queue.id = ? and queueItem.draftApplication.draftApplicationStatus.id <> ? and queueItem.draftApplication.draftApplicationStatus.id <> ? and queueItem.transaction.transactionStatus.id <> ? and queueItem.transaction.transactionStatus.id <> ? minus select transaction.id from queueItem where queueItem.itemType=? AND queueItem.queue.id=?


Hi,

Can any one please tell me whether HQL supports use of minus SQL keyword please?

My Actual SQL query

select trx_id
from QUEUE_ITEM this_ inner join TRANSACTION transactio1_ on this_.TRX_ID=transactio1_.ID
inner join DRAFT_APPLICATION draftappli2_ on transactio1_.DRA_ID=draftappli2_.ID
where this_.QUE_ID=100130 and this_.ITEM_TYPE='T'
and transactio1_.TRS_ID<>ANY(7,8)
and draftappli2_.ID<>ANY(4,5)
minus
select trx_id from queue_item where item_type='SI' AND que_id=100130

HQL Query

from QueueItem queueItem where queueItem.itemType = ? and queueItem.queue.id = ? and queueItem.draftApplication.draftApplicationStatus.id <> ? and queueItem.draftApplication.draftApplicationStatus.id <> ? and queueItem.transaction.transactionStatus.id <> ? and queueItem.transaction.transactionStatus.id <> ? minus select transaction.id from queueItem where queueItem.itemType=? AND queueItem.queue.id=?


Thanks
Prashant

im running into the same problem here. the MINUS-keyword seems not to be supported in hibernate. i could use a subselect with the IN key-word but running this statement over 2mio lines using MINUS takes 5 minutes while using IN + subselect takes forever.. :-/

hopefully someone can give a suggestion on this and hopefully there is a better solution than usig SQL instead of HQL.