I have two table, "Plan" and "TJianjinDan" . and they are also have a column called "PlanCode". but "TJianjinDan" is independent of "Plan",because the column "planCode" of TJianjinDan just have the same content with the column "planCode " of "Plan".

I write native sql below:
select t.planCode,max(t.boatName),max(t.cargoName),max(t.cargoSpec),count(t.carCode),sum(t.jingZhong),+
max(t.faHuoDanWei),max(t.shouHuoDanWei) from Plan as m left join TjianjinDan as t on m.planCode=t.planCode where t.secondTime between '2006-8-10' and '2006-9-20' and lower(t.planCode) like '%%'
group by t.planCode

YE,I work right in DBMS SUCH AS MSSQL.

But, when I write sql with hibernate below:

query=getHibernateTemplate().getSessionFactory().
getCurrentSession().createQuery("select t.planCode,max(t.boatName),max(t.cargoName),max(t.cargoSpec),count(t.carCode),sum(t.jingZhong)," +
" max(t.faHuoDanWei),max(t.shouHuoDanWei) from MainPlan m left join TjianjinDan t on m.planCode=t.planCode " +
" where t.secondTime between :beginTime and :endTime and lower(t.planCode) like:planCode " +
" group by t.planCode");
query.setTimestamp("beginTime", begin);
query.setTimestamp("endTime", end);
query.setString("planCode","%%"+ planCode+"%%");


OK,It cannot work. hibernate throw error below:
unexpected token: on near line 1, column 206.

yes,hibernate cannot identify the token "on" .how to fix this probem.

thank you!
asper

You are confusing SQL & HQL. HQL does not support the specification of join condition (ON ...) -- the join condition is specified in the mapping.

Your choices are:
1. Get the mappings correct and then use HQL;
2. Use Hibernate's native SQL support.

Review the manual for more info.