Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.1.x
Struts 1.2.7
Toomcat 5.5.12
MS SqlServer 2000

Hi..!!
i am working on an action which fetches data from database using join query.

In My Action I hv to fetch record from two tables;
1.AppMst
2.PaymentTranDraft
based on common AppMstId(int).

I need to convert following query in SQL form to HQL form:
SELECT am.AppMstRegNo as regno, am.AppMstFName as fname, am.AppMstLName as lname, am.AppMstCity as city, ptd.DispachedAmt as disam FROM AppMst AS am LEFT JOIN PaymentTranDraft AS ptd ON am.AppMstID = ptd.AppMstId where ptd.PaymentFromDate=? and ptd.PaymentToDate=? and am.AppMstPaid=? and am.AppMstActivate=? order by am.AppMstID

I had writen this in HQL in following way:

Session session=common.HibernateUtil.currentSession();
Transaction transaction = session.beginTransaction();
Query query=session.createQuery("from admin.PaidMemberListingForm as appmst left join admin.PaymentTranDraftBean as payTD appmst.txtIdNo, appmst.txtFName, appmst.txtLName, appmst.txtCity payTD.floatDispachAmount with appmst.appID=payTD.appID where payTD.txtPaymentFromDate>=:fromDate and payTD.txtPaymentToDate<=:toDate and appmst.numPaid=:AppMstPaid and appmst.numActivate=:AppMstActivate order by appmst.appID");
query.setString("fromDate",fromDate);
query.setString("toDate",toDate);
query.setInteger("AppMstPaid",1);
query.setInteger("AppMstActivate",1);

I am executing it by Iterator but it is Not Working.
The Error is:

e: java.lang.Exception = {org.hibernate.hql.ast.QuerySyntaxError@4375}"org.hibernate.hql.ast.QuerySyntaxError: unexpected token: appmst near line 1, column 90 [from admin.PaidMemberListingForm as appmst left join admin.PaymentTranDraftBean as payTD appmst.txtIdNo, appmst.txtFName, appmst.txtLName, appmst.txtCity payTD.floatDispachAmount with appmst.appID=payTD.appID where payTD.txtPaymentFromDate>=:fromDate and payTD.txtPaymentToDate<=:toDate and appmst.numPaid=:AppMstPaid and appmst.numActivate=:AppMstActivate order by appmst.appID]"

I have used Following Mapping Docs
Mapping documents:

AppMst.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping>
<class name="admin.PaidMemberListingForm" table="APPMST">
<id name="appID" type="integer" column="AppMstID" >
<generator class="increment"/>
</id>
<property name="txtIdNo"
column="AppMstRegNo"/>
<property name="txtFName">
<column name="AppMstFName"/>
</property>
<property name="txtLName">
<column name="AppMstLName"/>
</property>
<property name="txtCity">
<column name="AppMstCity"/>
</property>
<property name="txtState">
<column name="AppMstState"/>
</property>
<property name="txtPaidDate">
<column name="AppPaidDateTime"/>
</property>
<property name="numPaid">
<column name="AppMstPaid"/>
</property>
<property name="numActivate">
<column name="AppMstActivate"/>
</property>
</class>
</hibernate-mapping>

PaymentTranDraft.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping>
<class name="admin.PaymentTranDraftBean" table="PaymentTranDraft">
<id name="appID" type="integer" column="AppMstId" >
<generator class="increment"/>
</id>

<property name="txtDateFrom"
column="PaymentFromDate"/>
<property name="txtDateTo">
<column name="PaymentToDate"/>
</property>
<property name="floatDispachAmount">
<column name="DispachedAmt"/>
</property>

</class>
</hibernate-mapping>


Setters and Getters of properties in above mapping files are in,
admin.PaidMemberListingForm.java and admin.PaymentTranDraftBean.java respectively.


Name and version of the database you are using: SqlServer 2000


Plz help me to come over this problem.

Thanks in Advance.

Not sure, but is "with" a valid keyword?