On Mon, Feb 22, 2010 at 8:45 AM, Valentina Fabrizi < fabrizi_valent...@yahoo.it> wrote:
> 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 > > > > <dynamic prepend="LEFT OUTER JOIN rfx ON status.id_status = rfx.stato_rfx" > > > > .......TO SIMULATE THE BETWEEN CLAUSE....... > > <isNotNull prepend="and" property="dataCreazioneDa">and > rfx.data_creazione <![CDATA[>=]]> #dataCreazioneDa# </isNotNull> > > <isNotNull prepend="and" property="dataCreazioneA">and > rfx.data_creazione <![CDATA[<=]]> #dataCreazioneA# </isNotNull> > > ........CONDITIONS IF PARAMETER != NULL...... > > <isNotNull prepend="and" property="tipoRfx">and rfx.tipo_rfx = > #tipoRfx# </isNotNull> > > </dynamic> > > .....but I receive this exeption: > > > 2010-02-22 14:31:30,300 ERROR [STDERR] > *com.ibatis.common.jdbc.exception.NestedSQLException*: > > --- The error occurred while applying a parameter map. > > --- Check the cruscottoRfx.findCruscottoRfxDateCatMerc-InlineParameterMap. > > --- Check the statement (query failed). > > --- Cause: > *java.sql.SQLException*: ORA-00936: espressione mancante > > 2010-02-22 14:31:30,300 ERROR [STDERR] at > com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback( > *MappedStatement.java:201*) > > 2010-02-22 14:31:30,300 ERROR [STDERR] at > com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForList( > *MappedStatement.java:139*) > > 2010-02-22 14:31:30,300 ERROR [STDERR] at > com.ibatis.sqlmap.engine.mapping.statement.CachingStatement.executeQueryForList( > *CachingStatement.java:97*) > > 2010-02-22 14:31:30,300 ERROR [STDERR] at > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList( > *SqlMapExecutorDelegate.java:567*) > > 2010-02-22 14:31:30,300 ERROR [STDERR] at > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList( > *SqlMapExecutorDelegate.java:541*) > > 2010-02-22 14:31:30,300 ERROR [STDERR] at > com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList( > *SqlMapSessionImpl.java:118*) > > 2010-02-22 14:31:30,300 ERROR [STDERR] at > com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList( > *SqlMapClientImpl.java:94*) > > 2010-02-22 14:31:30,300 ERROR [STDERR] at > it.niuma.rfx.services.reportMgmtService.ReportMgmtServiceImpl.findCruscottoRfxDateCatMerc( > *ReportMgmtServiceImpl.java:133*) > > 2010-02-22 14:31:30,300 ERROR [STDERR] at > sun.reflect.NativeMethodAccessorImpl.invoke0( > *Native Method*) > > 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( > *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 > > Hi Valentina, I have something like this... hope this helps. SELECT c.COZCIA, c.COZANO, c.COZZNA, z.SMINF2, c.COZCCA, c.COZCCO, c.COZFCO, c.COZFCO, c.COZDCO, c.COZHCO, s.SACNSA, s.SACDSA, c.COZEOF FROM VPCOPA00.VTMCOZF c, VPCOPA00.VTMSACF s, OPF$pais$.LSLMAN01 z WHERE COZCIA = #pais:CHAR# AND COZANO = #anno:DECIMAL# AND c.COZZNA = z.SMSLSP AND c.COZANO = s.SACANO AND c.COZCIA = s.SACCIA AND c.COZZNA = s.SACZON AND c.COZCCO = s.SACCCO *<isParameterPresent>* <iterate prepend=" AND c.COZCCA IN " property="strCampannas" open="(" close=")" conjunction=","> #strCampannas[]# </iterate> <iterate prepend=" AND c.COZZNA IN " property="strZonas" open="(" close=")" conjunction=","> #strZonas[]# </iterate> <iterate prepend=" AND z.SMINF2 IN " property="strRegiones" open="(" close=")" conjunction=","> #strRegiones[]# </iterate> *</isParameterPresent> * ORDER BY c.COZCIA, c.COZANO, c.COZZNA, c.COZCCA, c.COZCCO
