I am new to hibernate. I have three tables to join and one of the table involves outer join.

MY SQL is like this,

SELECT DISTINCT FORM.FORNAME,
FORM.FORPKGNM,
FORM.FORPKGUM,
FORM.FOODMH,
FORM.FOUDIND,
ADMKEY
FROM IDVPCS01.ITDRUGS DRUG

JOIN IDVPCS01.ITFORM FORM ON DRUG.DMUMNEM = FORM.FORMNEM
LEFT OUTER JOIN (SELECT ADMKEY
FROM IDVPCS01.ITADMINV WHERE HOSPNO = '0455' AND WARDNO = 'A') TEMP
ON DRUG.DMADMKEY = ADMKEY WHERE FORM.FORNAME LIKE 'TYL%' ;

How do I wirte the mapping file for this? I have three databeans for each tables. Is the column names matters in the mapping file? Our table coumn names in join criteria is not same.

For e.g, from the above query FORMNEM - from FORM table, DMUMNEM - from Drug table. DMADMKEY - from Drug table, ADMKEY - from ADMINV table.

Can you post the database model so that we can try to understand what you are trying to model and maybe we could help you with this mapping?

Thank you for the message. We use DB2 V8.0 on z/OS. We connect this DB2 through JNDI call. I have three different individual mapping files for each table and no relation to the table mentioned in the mapping file.
My HQL as follows. I have the error in this HQL using 'ON' and '('. Can't I write the HQL this way?

String hql = " select distinct drugFormularyDB.formularyName, drugFormularyDB.packageNumber," +
" drugFormularyDB.unitofMedicine, drugFormularyDB.ODMHFlag, " +
" drugFormularyDB.updateIndicator, drugAdmInfoDB.ADMKey " +
" from DrugBasicInfoDataBean drugBasicInfoDB " +
" join DrugFormularyDataBean drugFormularyDB " +
" on drugBasicInfoDB.mnemonic = drugFormularyDB.mnemonic " +
" left outer join (select drugAdmInfoDB.ADMKey from DrugADMInfoDataBean drugAdmInfoDB" +
" where drugAdmInfoDB.hospitalNumber = :hospitalNumber " +
" and drugAdmInfoDB.unit = :unit)Temp " +
" on drugBasicInfoDB.ADMKey = drugAdmInfoDB.ADMKey" +
" where formularyName like :drugName ";

String drugName = dName.toUpperCase() + "%";
ArrayList drugFormularyDBList = (ArrayList) this.getHibernateTemplate()
.findByNamedParam(hql, "drugName", drugName);