Move your join constraints before the dynamic statements, the dynamic statements can then always use AND.
-----Original Message----- From: Gilles Schlienger [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 04, 2008 11:13 AM To: [email protected] Subject: Re : Dynamic request with null values and AND Thanks, I'm afraid this does not work, it generates a query like: select * from TABLE_1,TABLE_2 and RCC_FK_UTILISATEUR_USR_ID = USR_ID and USR_FK_CRCC_CRG_ID = CRG_ID that breaks. Any other thoughts? Thanks again in advance, Regards, Gilles ----- Message d'origine ---- De : Vinaya Tirikkovalluru <[EMAIL PROTECTED]> À : [email protected] Envoyé le : Lundi, 3 Mars 2008, 19h19mn 13s Objet : RE: Dynamic request with null values and AND Hi Gilles, Try <select id="selectRechercheUtilisateurs" parameterClass="xxx.xxx.xxx.xxx.recherche.RechercheUtilisateur" resultMap="utilisateurResultMap"> select * from TABLE_1,TABLE_2 <dynamic prepend="WHERE" > <isNotNull prepend="AND" property="cabinetId"> RTC_FK_CABINET_CBT_ID = #cabinetId# AND RTC_FK_UTILISATEUR_USR_ID = USR_ID </isNotNull> </dynamic> and RCC_FK_UTILISATEUR_USR_ID = USR_ID and USR_FK_CRCC_CRG_ID = CRG_ID </select> Vinaya -----Original Message----- From: Gilles Schlienger [mailto:[EMAIL PROTECTED] Sent: Monday, March 03, 2008 1:14 PM To: [email protected] Subject: Dynamic request with null values and AND Hi all, I'm using iBATIS in my application (and I love it) and my question is about using dynamic requests for an optimized research SQL request. I want to do the following: - if parameters are not null have dynamic WHERE clauses merged with my join criterias - if parameters are null, have just my join criterias My problem is that if all my dynamic attributes are null, I get a query in the form: SELECT XXX WHERE AND YYY and the query WHERE AND does not work... Would there be any clean way to ovoid this ? Thanks in advance Gilles >>> Here is my query: <select id="selectRechercheUtilisateurs" parameterClass="xxx.xxx.xxx.xxx.recherche.RechercheUtilisateur" resultMap="utilisateurResultMap"> select * from TABLE_1,TABLE_2 where <dynamic> <isNotNull prepend="AND" property="cabinetId"> RTC_FK_CABINET_CBT_ID = #cabinetId# AND RTC_FK_UTILISATEUR_USR_ID = USR_ID </isNotNull> </dynamic> and RCC_FK_UTILISATEUR_USR_ID = USR_ID and USR_FK_CRCC_CRG_ID = CRG_ID </select> ________________________________________________________________________ _____ Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail http://mail.yahoo.fr This electronic message is intended only for the use of the individual(s) or entity(ies) named above and may contain information which is privileged and/or confidential. If you are not the intended recipient, be aware that any disclosure, copying, distribution, dissemination or use of the contents of this message is prohibited. If you received this message in error, please notify the sender immediately. _____________________________________________________________________________ Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail http://mail.yahoo.fr
