I am attempting to write an Oracle query in HQL which is basically a joined view on the same table. I can generate the queries on the database using SQL in TOAD and they work fine, but I am finding it impossible to write the correct HQL query for use in Hibernate.

The query in SQL run through TOAD looks like this:-

--This one works but I assume we can't use the (+) notation in Hibernate queries
select a.label_id,a.text,a.locale, b.locale, b.text
from ITC_LABELS a, ITC_LABELS b where
a.label_id = b.label_id(+)
and a.label_id like 'Admin%'
and a.locale = 'uk'
and b.locale(+) = 'sg'

so here is a version in ansi SQL - not using the (+) syntax which also works:-

select a.label_id,a.text,a.locale, b.locale, b.text
from ITC_LABELS a left outer join ITC_LABELS b on
(a.label_id = b.label_id and b.locale = 'sg')
where a.label_id like 'Admin%'
and a.locale = 'uk'

Now here is my attempt using HQL through JAVA language:-

String QueryStr = "select a.status, a.labelName, a.text, b.text from ItcLabel a left outer join ItcLabel as b on (a.labelName = b.labelName and b.locale = '"+selectedLocale+"') where a.labelName like '"+labelName+"' and a.locale = '"+userLocale+"'";
System.out.println(QueryStr);
Query q = hs.createQuery(QueryStr);

(Some of the field names are different but that is only because they have different mapped names.)

I am getting the following error when I try this:-

2006-07-10 10:09:55,576 ERROR [org.apache.catalina.core.ContainerBase.[jboss.web].[localhost].[/shape].[action]] Servlet.service() for servlet action threw exception
org.hibernate.hql.ast.QuerySyntaxError: unexpected token: on near line 1, column 134 [select a.status, a.labelName, a.text, b.text from com.electrocomponents.broadvision.itc.dao.ItcLabel a left outer join ItcLabel as b on (a.labelName = b.labelName and b.locale = 'sg') where a.labelName like 'admin' and a.locale = 'uk']
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:63)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:215)

Any help you can give would be greatly appreciated. Many thanks.