Hi All,

I am working on a application in which i am using Hibernate 3.0 , for accessing the table i am using @NamedQueries. In this @NamedQueries i have multiple @NamedQuery with left join on three table , but when i execute the program it gives me some exception that is show below:-


18841 [pool-1-thread-1] INFO lAuthProviderWithProxyDetector(370) - The Locale has been set to : en
23772 [pool-1-thread-1] ERROR org.hibernate.hql.PARSER (33) - line 1:146: unexpected token: on
23772 [pool-1-thread-1] ERROR org.hibernate.hql.PARSER (33) - line 1:239: unexpected token: on
23772 [pool-1-thread-1] ERROR org.hibernate.hql.PARSER (33) - line 1:320: unexpected token: on
23773 [pool-1-thread-1] ERROR org.hibernate.hql.PARSER (33) - line 1:139: unexpected token: on
23773 [pool-1-thread-1] ERROR org.hibernate.hql.PARSER (33) - line 1:233: unexpected token: on
23773 [pool-1-thread-1] ERROR org.hibernate.hql.PARSER (33) - line 1:315: unexpected token: on
23788 [pool-1-thread-1] ERROR org.hibernate.hql.PARSER (33) - line 1:145: unexpected token: on
23788 [pool-1-thread-1] ERROR org.hibernate.hql.PARSER (33) - line 1:238: unexpected token: on
23804 [pool-1-thread-1] ERROR org.hibernate.hql.PARSER (33) - line 1:319: unexpected token: on
23804 [pool-1-thread-1] ERROR ernate.impl.SessionFactoryImpl(363) - Error in named query: findBudgetLockValues
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 146 [SELECT opHeader.budget_Pos_Lock FROM org.unhcr.omss.datamodel.operation.OperationPlanHeader opHeader left join OperationHeader operationHeader on opheader.operationheader_id= operationheader.id left join PlanningPeriod planningPeriod on opheader.planningperiod_id = planningperiod.id left join plantypes plantype on opheader.plantype_id = plantype.id WHERE operationHeader.id IN (:addParameter) and planningPeriod.ID = :addPlanYearId and planType.ID = :addPlanTypeId]
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31)
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:24)...........


23804 [pool-1-thread-1] ERROR ernate.impl.SessionFactoryImpl(363) - Error in named query: findPlanLockValues
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 139 [SELECT opHeader.plan_Lock FROM org.unhcr.omss.datamodel.operation.OperationPlanHeader opHeader left join OperationHeader operationHeader on opheader.operationheader_id= operationheader.id left join PlanningPeriod planningPeriod on opheader.planningperiod_id = planningperiod.id left join plantypes plantype on opheader.plantype_id = plantype.id WHERE operationHeader.id IN (:addParameter) and planningPeriod.ID = :addPlanYearId and planType.ID = :addPlanTypeId]
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31).........

23804 [pool-1-thread-1] ERROR ernate.impl.SessionFactoryImpl(363) - Error in named query: findIndicatorLockValues
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 145 [SELECT opHeader.indicator_Lock FROM org.unhcr.omss.datamodel.operation.OperationPlanHeader opHeader left join OperationHeader operationHeader on opheader.operationheader_id= operationheader.id left join PlanningPeriod planningPeriod on opheader.planningperiod_id = planningperiod.id left join plantypes plantype on opheader.plantype_id = plantype.id WHERE operationHeader.id IN (:addParameter) and planningPeriod.ID = :addPlanYearId and planType.ID = :addPlanTypeId]
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31)..........


My Named Queries are:-

@NamedQuery(name="findPlanLockValues",query="SELECT opHeader.plan_Lock FROM OperationPlanHeader opHeader left join OperationHeader operationHeader on opheader.operationheader_id= operationheader.id left join PlanningPeriod planningPeriod on opheader.planningperiod_id = planningperiod.id left join plantypes plantype on opheader.plantype_id = plantype.id WHERE operationHeader.id IN (:addParameter) and planningPeriod.ID = :addPlanYearId and planType.ID = :addPlanTypeId"),

@NamedQuery(name="findBudgetLockValues",query="SELECT opHeader.budget_Pos_Lock FROM OperationPlanHeader opHeader left join OperationHeader operationHeader on opheader.operationheader_id= operationheader.id left join PlanningPeriod planningPeriod on opheader.planningperiod_id = planningperiod.id left join plantypes plantype on opheader.plantype_id = plantype.id WHERE operationHeader.id IN (:addParameter) and planningPeriod.ID = :addPlanYearId and planType.ID = :addPlanTypeId"),

@NamedQuery(name="findIndicatorLockValues",query="SELECT opHeader.indicator_Lock FROM OperationPlanHeader opHeader left join OperationHeader operationHeader on opheader.operationheader_id= operationheader.id left join PlanningPeriod planningPeriod on opheader.planningperiod_id = planningperiod.id left join plantypes plantype on opheader.plantype_id = plantype.id WHERE operationHeader.id IN (:addParameter) and planningPeriod.ID = :addPlanYearId and planType.ID = :addPlanTypeId"),


Same Queries as SQL Query are:-

SELECT opheader.plan_lock,opheader.id
FROM OperationPlanHeader opHeader left join OperationHeader operationHeader
on opheader.operationheader_id= operationheader.id
left join PlanningPeriod planningPeriod
on opheader.planningperiod_id = planningperiod.id
left join plantypes plantype
on opheader.plantype_id = plantype.id
WHERE operationHeader.id IN ('7XS', '7XB', '7YE', '7Z1', '7WC', '7WH', '7Y8', '7Y6', '7VX')
and planningPeriod.ID = 'LYPF'
and planType.ID = 'P-8'


same queries will be used for budget_Pos_Lock and indicator_Lock with single column change.


By seeing exception it looking like it's a syntax error in @NamedQuery for using joins between multiple table.Please guide me to write correct @NamedQuery with multiple table joins and where multiple conditions.


Thanks in advance........ :)


Regards,
Sachin