ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "meindert" <meind...@eduflex.com>
Subject RE: How to map a dynamic condition on Left Outer Join
Date Mon, 22 Feb 2010 14:13:08 GMT
That’s strange, it is just as straightforward as the code shows...

What does the query look like in the log file?

 

Must say I’m using <dynamic> a lot myself..

 

Sorry

 Meindert

 

 

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

 

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   <http://mail.yimg.com/a/i/mesg/tsmileys2/02.gif>


 

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

 

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


Mime
View raw message