I need to write an outer join query with a restriction on an inner table.
Written in sybase the query would look like this:

select I.*
from ITEM I left outer join BID B on I.ITEM_ID = B.ITEM_ID
where B.amount *= 10

How can this query be expressed in HQL?
Or does HQL follow strict ANSI standards that prohibit usage of inner table in any where clause conditons? If so is there a work around?

Thank you.

Hibernate3 adds a "WITH" clause to joins, which is what you want. Check the docs

Hi, Steve!

Thank you for reply. I found "with" keyword example on page 139 of Hibernate3 reference guide under section "15.3 Associations and joins".

I ran my query with it and got an error: unexpected token: with ...

Here is the detailed error message for my query:

=======================================
10:18:20,655 ERROR PARSER:35 - *** ERROR: line 1:137: unexpected token: with
org.hibernate.hql.ast.QuerySyntaxError: unexpected token: with near line 1, column 137 [select feed, feedinstance from com.csfb.fao.clr.model.FeedStatic as feed left outer join feed.feedInstances as feedinstance with feedinstance.mostRecent='Y']
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:63)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:215)
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:421)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:824)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:782)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at Main.test5(Main.java:192)
at Main.main(Main.java:43)
Caused by: line 1:137: unexpected token: with
==========================================

I then proceeded to eliminate the "with" token, and the query ran just fine without this restriction! It correctly returned a combinaton of FeedStatic abd FeedInstance objects and I was able to process them.

Do you know why I am not able to utilize the "with" keyword? The doc has just one example on its usage. Is there anything else I might be missing?

Thank you.

most likely you are not using a version of Hibernate that supports the WITH keyword :)

Steve,

We are using version 3.0.1, here is the Manifest file from Hibernate3.jar:

=============================
Manifest-Version: 1.0
Ant-Version: Apache Ant 1.6.2
Created-By: 1.4.2-38 ("Apple Computer, Inc.")
Hibernate-Version: 3.0.1
=============================

Should we switch to a later version? I am not sure where to obtain the documentation on versions and features. Is there a release notes web page for Hibernate versions?

The reference guide describing "with" key word is for v3.1 beta:

http://docs.jboss.org/ejb3/app-server/H ... erence.pdf

Is this the first version supporting "with"?

Thank you.

Note that it is not enough to use H3, you must also set the correct Query Translator Factory in the hibernate.properties file.
I had the same problem here: https://forum.hibernate.org/viewtopic.php?f=1&t=1005758&start=0