ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Fernando Rengifo <freng...@gmail.com>
Subject Re: How to map a dynamic condition on Left Outer Join
Date Mon, 22 Feb 2010 14:08:25 GMT
On Mon, Feb 22, 2010 at 8:45 AM, Valentina Fabrizi <
fabrizi_valentina@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 <meindert@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_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-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

Mime
View raw message