Hibernate version: 3.0.x

Name and version of the database you are using:Oracle 10g

for the sql written below, I created the HQL :

*************************************
select searchtypeid , pageid, ms.market_segment_name , flighttargetid, sum(eventcount) as ec

from adsinv.ads_inv_data_h_mv inv inner join adsinv.ads_market_segments ms

on inv.MODELID = ms.SEGMENTATION_VARIABLE_ID

where
siteid=2797 and adsizeid=2820 and pageid in (2716,2812) and modelid in (1331, 2611, 1392 )

and dmaid=588 and searchtypeid in (2800, 2803,2801) and searchtypeid in (2803)

and market_segment_name in ('DODGE - CARS' , 'JAGUAR - CARS' )

group by searchtypeid, pageid,ms.market_segment_name, flighttargetid

order by searchtypeid, pageid,ms.market_segment_name, flighttargetid

*****************************************
HQL :

******************************************
select dm.flighttargetid, sum(dm.eventcount) ,dm.searchtypeid,ms.market_segment_name
from AdsInvSearchData dm
inner join AbstractMarketSegmentData ms on
dm.modelid = ms.segmentationVariableId
where
dm.adsizeid=1969 and dm.siteid=2796 and dm.searchtypeid in (2802,2800) and dm.modelid in (2579,2580)and ms.market_segment_name in ('BMW L Series')
group by dm.flighttargetid ,dm.searchtypeid,ms.market_segment_name
order by dm.searchtypeid,dm.flighttargetid,ms.market_segment_name
****************************************

This is throwing following error:

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 3, column 41 [select dm.flighttargetid, sum(dm.eventcount) ,dm.searchtypeid,ms.market_segment_name
from com.atc.adsinv.entity.AdsInvSearchData dm
inner join AbstractMarketSegmentData ms on
dm.modelid = ms.segmentationVariableId
where
dm.adsizeid=1969 and dm.siteid=2796 and dm.searchtypeid in (2802,2800) and dm.modelid in (2579,2580)and ms.market_segment_name in ('BMW L Series')
group by dm.flighttargetid ,dm.searchtypeid,ms.market_segment_name
order by dm.searchtypeid,dm.flighttargetid,ms.market_segment_name

]


am I doing something wrong here. Is "inner join on" not supported in hibernate? If yes, then whats the alternative.

Any direction will be really helpful.

As far as I know, one of the main advantages of hibernate is, precisely, the fact that you don't have to specify the join columns in queries, because, in theory, they were already specified, only once, in the mapping file.

If the join you want to achieve is uncommon or can't be expressed in the mapping file, what you can do is specify the join in the "WHERE ..." clause, as if it were another where condition. In the Hibernate documentation they call this kind of join a "theta join".

In other words, if you have 2 tables



and, for some reason, the relationship between A and B is not specified in your map file,you can always write


Gonzalo Díaz

if you define the join in where clause then how do you specify inner, left or right join with it.

In principle that is not possible in HQL yet (the only theta joins supported are inner joins).
But you could define a named query, using your database's SQL.

Hi,
If you dont have any mapping for these join,please use Native Query.In which you can write the same SQL query for retreiving data with Hibernate features.
if you want to know more about NativeQuery pls go the 16 th chapter of the given Link
http://www.hibernate.org/hib_docs/refer ... rysql.html