Hi,

I have to do query from 2 table.

config = new NHibernate.Cfg.Configuration();
config.AddAssembly(typeof(CSTS.Main.CashPosting).Assembly);
factory = config.BuildSessionFactory();
session = factory.OpenSession();
query = session.CreateQuery("FROM CashPosting C INNER JOIN FundGroup F On C.Acct_Cd=F.Acct.Cd WHERE F.Acct_Grp_Cd='ASPRUI'");


I am getting error

outer or full join must be followed by path expression [FROM CSTS.Main.CashPosting C INNER JOIN FundGroup F On C.Acct_Cd=F.Acct.Cd WHERE F.Acct_Grp_Cd='ASPRUI']


How can I add the Assembly of Fundgroup???

Below is mappping of Fundgroup

<?xml version="1.0" encoding="utf-8" ?>
<!-- Generated by MoreGen 29-Jan-2009 12:20:37 -->
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" schema="tm_dev" default-cascade="save-update" auto-import="true" >
<class name="CSTS.Main.FundGroup, CSTS.Main" lazy="true" table="CSTS_M_FUNDGROUP">
<id name="Acct_Grp_Cd" type="string" column="ACCT_GRP_CD" access="field.pascalcase-underscore">
<generator class="assigned" />
</id>
<property name="Acct_Grp_Name" column="ACCT_GRP_NAME" access="field.pascalcase-underscore" />
<property name="Acct_Cd" column="ACCT_CD" access="field.pascalcase-underscore" />

</class>
</hibernate-mapping>

When you use HQL you can only join mapped collections. In your case try:

FROM CashPosting C, FundGroup F WHERE C.Acct_Cd=F.Acct.Cd and F.Acct_Grp_Cd='ASPRUI'

This query will return a list of object[] which will contain pairs of CashPosting and FundGroup.

Can we do join using CreateCriteria??

No, criteria also work on mapped properties/collections. Are CashPosting and FundGroup conntected in the mapping files ?

no, both mapping file are not connected. Below is the 2nd table, How can we map both table so we can do the query.

<?xml version="1.0" encoding="utf-8" ?>
<!-- Generated by MoreGen 5-Feb-2009 02:28:36 -->
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" schema="tm_dev" default-cascade="save-update" auto-import="true">
<class name="CSTS.Main.CashPosting, CSTS.Main" lazy="true" table="CSTS_T_CASH_POSTING">
<id name="Cash_Cd" type="double" column="CASH_CD" access="field.pascalcase-underscore">
<generator class="assigned" />
</id>
<property name="Acct_Cd" column="ACCT_CD" access="field.pascalcase-underscore" />
<property name="Agent_Cd" column="AGENT_CD" access="field.pascalcase-underscore" />
<property name="Amount" column="AMOUNT" access="field.pascalcase-underscore" />
<property name="Checker_User" column="CHECKER_USER" access="field.pascalcase-underscore" />
<property name="Crrncy_Cd" column="CRRNCY_CD" access="field.pascalcase-underscore" />
<property name="Est_Conf" column="EST_CONF" access="field.pascalcase-underscore" />
<property name="Last_Upd_Date" column="LAST_UPD_DATE" access="field.pascalcase-underscore" />
<property name="Last_Upd_User" column="LAST_UPD_USER" access="field.pascalcase-underscore" />
<property name="DAYEND_DATE" column="DAYEND_DATE" access="field.pascalcase-underscore" />
</class>
</hibernate-mapping>

Now I have mapped the both table.

<?xml version="1.0" encoding="utf-8" ?>
<!-- Generated by MoreGen 5-Feb-2009 02:28:36 -->
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" schema="tm_dev" default-cascade="save-update" auto-import="true">
<class name="CSTS.Main.CashPosting, CSTS.Main" lazy="true" table="CSTS_T_CASH_POSTING">
<id name="Cash_Cd" type="double" column="CASH_CD" access="field.pascalcase-underscore">
<generator class="assigned" />
</id>
<property name="Acct_Cd" column="ACCT_CD" access="field.pascalcase-underscore" />
<property name="Agent_Cd" column="AGENT_CD" access="field.pascalcase-underscore" />
<property name="Amount" column="AMOUNT" access="field.pascalcase-underscore" />
<property name="Checker_User" column="CHECKER_USER" access="field.pascalcase-underscore" />
<property name="Crrncy_Cd" column="CRRNCY_CD" access="field.pascalcase-underscore" />
<property name="Est_Conf" column="EST_CONF" access="field.pascalcase-underscore" />
<property name="Last_Upd_Date" column="LAST_UPD_DATE" access="field.pascalcase-underscore" />
<property name="Last_Upd_User" column="LAST_UPD_USER" access="field.pascalcase-underscore" />


<many-to-one name="CSTS.Main.FundGroup" column="Acct_Cd" class="CSTS.Main.FundGroup" />


</class>
</hibernate-mapping>




<?xml version="1.0" encoding="utf-8" ?>
<!-- Generated by MoreGen 29-Jan-2009 12:20:37 -->
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" schema="tm_dev" default-cascade="save-update" auto-import="true" >
<class name="CSTS.Main.FundGroup, CSTS.Main" lazy="true" table="CSTS_M_FUNDGROUP">
<id name="Acct_Grp_Cd" type="string" column="ACCT_GRP_CD" access="field.pascalcase-underscore">
<generator class="assigned" />
</id>
<property name="Acct_Grp_Name" column="ACCT_GRP_NAME" access="field.pascalcase-underscore" />
<property name="Acct_Cd" column="ACCT_CD" access="field.pascalcase-underscore" />
<bag name="CSTS_T_CASH_POSTING" inverse="true">
<key column="Acct_Cd" />
<one-to-many class="CSTS.Main.CashPosting" />
</bag>

</class>
</hibernate-mapping>


Now how can we do the query???

FROM CashPosting C INNER JOIN C.FundGroup F WHERE F.Acct_Grp_Cd='ASPRUI'

Hi,

I am getting error message.

Could not compile the mapping document..

Maybe the error message would help ....

Could not compile the mapping document