Hi Valentina,


ibatis does not care/check where you put the tags in you sql query.

I don’t think you want to use a dynamic tag in your example;

and #dataCreazioneA# <isNotNull property="tipoRfx"> AND rfx.tipo_rfx = #tipoRfx#</isNotNull>

woud work just fine

as far as I know you only use dynamic to cut off the first ‘AND’ to make the where clause correct.




From: Valentina Fabrizi [mailto:fabrizi_valentina@yahoo.it]
Sent: 22 February 2010 01:23 PM
To: user-java@ibatis.apache.org
Subject: How to map a dynamic condition on Left Outer Join


Hello and have a nice day!


I try to use iBatis for my web-app reporting section.

I need to add a dynamic condition on a LEFT OUTER JOIN not in a WHERE clause.....


I'm using ibatis-




<select id="findCruscottoRfxDateCatMerc" cacheModel="cruscottoRfxDateCatMercCache" resultMap="cruscottoRfxResult" parameterClass="java.util.Map">


SELECT status.nome as STATO,

           status.id_status as ID_STATUS,

           COUNT(distinct rfx_cat_merc.id_rfx) as NUMERO_RFX,

           to_char(SUM(to_number(replace(risposta_rfx_riferimento.risposta_stringa, '.', '')))) as VALORE_BUDGET,

           to_char(AVG(to_number(replace(risposta_rfx_riferimento.risposta_stringa, '.', '')))) as VALORE_MEDIO_BUDGET,

           to_char(SUM(to_number(replace(risposta.risposta_stringa, '.', '')))) as VALORE_OFFERTE,

           to_char(SUM(to_number(replace(risposta_for_aggiudicatario.risposta_stringa, '.', '')))) as VALORE_AGGIUDICATO,

           to_char(SUM(to_number(replace(risposta_rfx_riferimento.risposta_stringa, '.', ''))) - SUM(to_number(replace(risposta_for_aggiudicatario.risposta_stringa, '.', '')))) as SAVING,

           to_char((SUM(to_number(replace(risposta_rfx_riferimento.risposta_stringa, '.', ''))) - SUM(to_number(replace(risposta_for_aggiudicatario.risposta_stringa, '.', '')))) / SUM(to_number(replace(risposta_rfx_riferimento.risposta_stringa, '.', '')))) as SAVING_PERCENTUALE


FROM status

        LEFT OUTER JOIN rfx ON status.id_status = rfx.stato_rfx and rfx.data_creazione between #dataCreazioneDa# and #dataCreazioneA# ???? <dynamic><isNotNull prepend="and" property="tipoRfx">rfx.tipo_rfx = #tipoRfx#</isNotNull></dynamic> ????

        LEFT OUTER JOIN rfx_cat_merc ON rfx.id_rfx = rfx_cat_merc.id_rfx and rfx_cat_merc.id_cat_merc = #catMerc#

        LEFT OUTER JOIN rfx_compilata ON rfx.id_rfx = rfx_compilata.id_rfx and rfx.id_rfx = rfx_cat_merc.id_rfx

        LEFT OUTER JOIN risposta ON rfx_compilata.id_rfx_compilata = risposta.id_rfx_compilata and risposta.id_item in ('Valore_Totale_Offerta_id', 'Total_Value_Offers_id', 'Valeur_Total_Offre_id')

        LEFT OUTER JOIN rfx_riferimento ON rfx.id_rfx = rfx_riferimento.ID_RFX and rfx.id_rfx = rfx_cat_merc.id_rfx

        LEFT OUTER JOIN risposta_rfx_riferimento ON rfx_riferimento.ID_RFX_RIFERIMENTO = risposta_rfx_riferimento.ID_RFX_RIFERIMENTO and risposta_rfx_riferimento.id_item in ('Valore_Totale_Offerta_id', 'Total_Value_Offers_id', 'Valeur_Total_Offre_id')

        LEFT OUTER JOIN utente ON rfx.fornitore_aggiudicatario = utente.id_fornitore

        LEFT OUTER JOIN rfx_compilata compilata_for_aggiudicatario ON utente.id_utente = compilata_for_aggiudicatario.compilata_da and rfx.id_rfx = compilata_for_aggiudicatario.id_rfx and rfx_compilata.id_rfx_compilata = compilata_for_aggiudicatario.id_rfx_compilata and rfx.id_rfx = rfx_cat_merc.id_rfx

        LEFT OUTER JOIN risposta risposta_for_aggiudicatario ON compilata_for_aggiudicatario.id_rfx_compilata = risposta_for_aggiudicatario.id_rfx_compilata and risposta_for_aggiudicatario.id_item in ('Valore_Totale_Offerta_id', 'Total_Value_Offers_id', 'Valeur_Total_Offre_id')


GROUP BY status.nome, status.id_status

ORDER BY status.id_status





Cheers, Valentina




No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.733 / Virus Database: 271.1.1/2702 - Release Date: 02/21/10 21:34:00