Hi all,
Hibernate version : 3.0.5
DBMS : Oraclce 9i

Iam having the problem in using the criteria Queries, In my project Iam having three table in which one is master table and other two table has a relation ship with this master table

parent --------------------- child1
one to one

parent --------------------- child2
one to many


consider parent = T_Quotation_mstr table
child1 = t_error_auth_dtls
child2 = t_quotation_acct_dtls


and I used the following

Criteria criteriaCheque = session.createCriteria(RezAffiliateDetailsBean.class)
.add(Restrictions.eq("accountAssistantCode",new Integer(objOPCorrectionViewBean.getAccountAssistantCode())));


Criteria subCriteriaErrorAuth = criteriaCheque.createCriteria("objErrorAuthBean")
.setFetchMode("objErrorAuthBean",FetchMode.JOIN)
.add(Restrictions.conjunction().add(Restrictions.eq("errorcode",new Long(objOPCorrectionViewBean.getErrorcode())))
.add(Restrictions.ne("authorisationFlag",new Short((short)1))));

Criteria subCriteriaQuotationAcctDetails = criteriaCheque.createCriteria("objQuotationAcctDetailsBean")
.setFetchMode("objQuotationAcctDetailsBean",FetchMode.JOIN)
.add(Restrictions.disjunction().add(Restrictions.eq("accountTypeId",new Short((short)1))));



Criteria subCriteriaQuotationAcctDetails1 = criteriaCheque.createCriteria("objQuotationAcctDetailsBean")
.add(Restrictions.disjunction().add(Restrictions.not(
Restrictions.in("accountTypeId",new Short[]{new Short((short)2),new Short((short)3),new Short((short)4),new Short((short)5)}))));


The sql generated by the Hibernate automatically appends "AND" in the where clause of the SQL I need OR condition instead AND in the where clause of the SQL,

I know that OR can be add between the two condition in single create criteria I need and condition between the two Sub criteria (In this case i need OR condition between the subCriteriaQuotationAcctDetails1 and subCriteriaErrorAuth )

please kindly do the needful...

for the information the SQL generated by the Hibernate is displayed below


select *
from (select this_.QTM_QTN_ID as QTM1_2_,
this_.QTM_AFF_RUT_ID as QTM2_3_2_,
this_.QTM_ROL_FOL_NN as QTM3_3_2_,
this_.QTM_IMG_FOL_NN as QTM4_3_2_,
this_.QTM_SEQ_NN as QTM5_3_2_,
this_.QTM_QTN_PRD_NN as QTM6_3_2_,
this_.QTM_PMT_FC as QTM7_3_2_,
this_.QTM_AFF_NM as QTM8_3_2_,
this_.QTM_PYR_RUT_ID as QTM9_3_2_,
this_.QTM_ACC_ASS_CD as QTM10_3_2_,
this_.EAD_ERR_AUT_NN as EAD11_3_2_,
quotationa2_.QAD_QTN_ACC_ID as QAD1_0_,
quotationa2_.QTM_QTN_ID as QTM2_5_0_,
quotationa2_.ATM_ACC_TYP_ID as ATM3_5_0_,
quotationa2_.QAD_DFF_ADQ_$$ as QAD4_5_0_,
quotationa2_.QAD_DFF_QOT_$$ as QAD5_5_0_,
quotationa2_.QAD_PMT_TYP_RE as QAD6_5_0_,
quotationa2_.QAD_ADL_QOT_$$ as QAD7_5_0_,
quotationa2_.QAD_ADL_PNP_$$ as QAD8_5_0_,
quotationa2_.QAD_PEN_QOT_$$ as QAD9_5_0_,
quotationa2_.QAD_QTN_QOT_$$ as QAD10_5_0_,
errorauthb1_.EAD_ERR_AUT_NN as EAD1_1_,
errorauthb1_.ERM_ERR_ID as ERM2_2_1_,
errorauthb1_.AXM_AUX_ID as AXM3_2_1_,
errorauthb1_.EAD_ATH_RE as EAD4_2_1_,
errorauthb1_.EAD_ATH_FC as EAD5_2_1_,
errorauthb1_.QTM_QTN_ID as QTM6_2_1_
from T_QUOTATION_MSTR this_ inner join T_QUOTATION_ACCT_DTLS quotationa2_ on this_.QTM_QTN_ID = quotationa2_.QTM_QTN_ID inner join T_ERROR_AUTH_DTLS errorauthb1_ on this_.QTM_QTN_ID = errorauthb1_.QTM_QTN_ID
where this_.QTM_ACC_ASS_CD = ? and
(errorauthb1_.ERM_ERR_ID = ? and errorauthb1_.EAD_ATH_RE <> ?) and
(quotationa2_.ATM_ACC_TYP_ID = ?))


I need the OR condition in the Where clause
where this_.QTM_ACC_ASS_CD = ? and
(errorauthb1_.ERM_ERR_ID = ? and errorauthb1_.EAD_ATH_RE <> ?) AND (quotationa2_.ATM_ACC_TYP_ID = ?))

instead of this and I need

where this_.QTM_ACC_ASS_CD = ? and
(errorauthb1_.ERM_ERR_ID = ? and errorauthb1_.EAD_ATH_RE <> ?) OR
(quotationa2_.ATM_ACC_TYP_ID = ?))







[/list]