Hi Meindert, thanks for your reply! I try to use <isNotNull property="tipoRfx"> AND rfx.tipo_rfx = #tipoRfx#</isNotNull> with or without the dynamic tag but the result is the same.....it doesn't work Now....I try to use this mapping.... 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 .......TO SIMULATE THE BETWEEN CLAUSE....... <isNotNull prepend="and" property="dataCreazioneDa">and rfx.data_creazione <![CDATA[>=]]>#dataCreazioneDa# </isNotNull> ........CONDITIONS IF PARAMETER != NULL...... <isNotNull prepend="and" property="tipoRfx">and rfx.tipo_rfx = #tipoRfx# </isNotNull></dynamic> <isNotNull prepend="and" property="dataCreazioneA">and rfx.data_creazione <![CDATA[<=]]>#dataCreazioneA# </isNotNull><dynamic prepend="LEFT OUTER JOIN rfx ON status.id_status = rfx.stato_rfx">
.....but I receive this exeption: 2010-02-22 14:31:30,300 ERROR [STDERR] --- The error occurred while applying a parameter map. --- Check the cruscottoRfx.findCruscottoRfxDateCatMerc-InlineParameterMap. --- Check the statement (query failed). --- Cause: 2010-02-22 14:31:30,300 ERROR [STDERR] at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback( 2010-02-22 14:31:30,300 ERROR [STDERR] at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForList( 2010-02-22 14:31:30,300 ERROR [STDERR] at com.ibatis.sqlmap.engine.mapping.statement.CachingStatement.executeQueryForList( 2010-02-22 14:31:30,300 ERROR [STDERR] at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList( 2010-02-22 14:31:30,300 ERROR [STDERR] at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList( 2010-02-22 14:31:30,300 ERROR [STDERR] at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList( 2010-02-22 14:31:30,300 ERROR [STDERR] at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList( 2010-02-22 14:31:30,300 ERROR [STDERR] at it.niuma.rfx.services.reportMgmtService.ReportMgmtServiceImpl.findCruscottoRfxDateCatMerc( 2010-02-22 14:31:30,300 ERROR [STDERR] at sun.reflect.NativeMethodAccessorImpl.invoke0( 2010-02-22 14:31:30,300 ERROR [STDERR] at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) 2010-02-22 14:31:30,300 ERROR [STDERR] at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) 2010-02-22 14:31:30,300 ERROR [STDERR] at java.lang.reflect.Method.invoke(Unknown Source) 2010-02-22 14:31:30,300 ERROR [STDERR] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(com.ibatis.common.jdbc.exception.NestedSQLException: java.sql.SQLException: ORA-00936: espressione mancanteMappedStatement.java:201)MappedStatement.java:139)CachingStatement.java:97)SqlMapExecutorDelegate.java:567)SqlMapExecutorDelegate.java:541)SqlMapSessionImpl.java:118)SqlMapClientImpl.java:94)ReportMgmtServiceImpl.java:133)Native Method)AopUtils.java:310) Cheers, Valentina ________________________________ Da: meindert <meind...@eduflex.com> A: user-java@ibatis.apache.org Inviato: Lun 22 febbraio 2010, 13:49:47 Oggetto: RE: How to map a dynamic condition on Left Outer Join 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. Meindert From:Valentina Fabrizi [mailto:fabrizi_valent...@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-2.3.4.726.jar Query: <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 </select> Thanks! 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
