'on' clause usage - Hibernate dynamic query translator

nandyala · **Joined:** Fri Mar 03, 2006 2:11 pm **Posts:** 16

Hibernate version: 3.1

Name and version of the database you are using: DB2 Version 8.x
When I try to use Dynamic query translator with the following SQL query I get errors:
select acc.username from Account acc join Profile prof on acc.userid=prof.userid

It is failing at 'ON' clause and I do not see any error in SQL syntax. Please note that all classes used in thsi SQL query are available in the workspace/project

Full stack trace of any exception that occurs:
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 74 [select acc.username from com.ibm.hibernate.Account acc join Profile prof on acc.userid=prof.userid

]
Caused by:
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 74 [select acc.username from com.ibm.hibernate.Account acc join Profile prof on acc.userid=prof.userid

]

tbech · **Joined:** Fri Oct 28, 2005 7:11 am **Posts:** 20

use 'with' clause

nandyala · **Joined:** Fri Mar 03, 2006 2:11 pm **Posts:** 16

tbech wrote:

use 'with' clause

When I use 'with' clause I get this error message:
Caused by:
org.hibernate.hql.ast.QuerySyntaxException: Path expected for join! [Select act.firstname from com.ibm.hibernate.Account act join Profile prof with act.userid=prof.userid]

Thank you

nathanmoon · **Posted:** Mon Apr 03, 2006 1:28 pm

A normal join would look something like this:

Code:

select acc.username from Account acc join acc.profile prof

(assuming Account has an association to Profile named 'profile')

If you want to have extra join conditions, you can use 'with', but as far as I know, you can't join across two unassociated classes. The association must be there in the mapping files (e.g. Account.profile). And you must use that association in the join ( 'join acc.profile', not just 'join Profile').