I am trying to duplicate an HQL join example from the manual as below
in my code but am having no luck, to pull data from two related tables via a join

http://www.hibernate.org/hib_docs/v3/re ... ryhql.html
select cust
from Product prod,
Store store
inner join store.customers cust
where prod.name = 'widget'
and store.location.name in ( 'Melbourne', 'Sydney' )
and prod = all elements(cust.currentOrder.lineItems)

and am trying to do similar, and select data from both tables only for
a specific country.

I have two tables

DATATYPE :
hjid bigint NOT NULL ( KEY )
hjtype character varying(255) NOT NULL
country character varying(255)
comments character varying(255)
programme character varying(255)

REVENUECOSTSFUELTYPE :
hjid bigint NOT NULL
hjtype character varying(255) NOT NULL
type character varying(255)
comments character varying(255)
value real
samplingparams bigint
datatype_revenuecostsfuel_hjid bigint ( FOREIGN_KEY FOR DATATYPE TABLE)
datatype_revenuecostsfuel_hjindex
grouping bigint


My query is

'select data from generated.DATA data inner join
generated.REVENUECOSTSFUEL sub on data.hjid =
sub.datatype_revenuecostsfuel_hjid and data.COUNTRY=spain

i get :
<java.lang.Exception: java.lang.Exception: java.lang.Exception: Error
looking for existing data :
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: where
near line 1, column 80


I would be glad for any assistance

Thanks

Paul.

Have you tried something like:



I think the HQL Reference Manual section 14.8 http://www.hibernate.org/hib_docs/v3/reference/en/html/queryhql.html#queryhql-select is useful


John.

'select data from generated.DATA data inner join
generated.REVENUECOSTSFUEL sub on data.hjid =
sub.datatype_revenuecostsfuel_hjid and data.COUNTRY=spain

Here spain is string put that in ' (single quotes)

Change to following and check

'select data from generated.DATA data inner join
generated.REVENUECOSTSFUEL sub on data.hjid =
sub.datatype_revenuecostsfuel_hjid and data.COUNTRY='spain'

single quote is missing for spain