ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Valentina Fabrizi <fabrizi_valent...@yahoo.it>
Subject How to map a dynamic condition on Left Outer Join
Date Tue, 23 Feb 2010 08:22:41 GMT


 
Hi!
I solved my problem with this mapping:
 
 
<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        <isNotNull prepend="and"
property="dataCreazioneDa">rfx.data_creazione <![CDATA[>=]]>#dataCreazioneDa#
</isNotNull>        <isNotNull prepend="and" property="dataCreazioneA">rfx.data_creazione
<![CDATA[<=]]>#dataCreazioneA# </isNotNull>        <isNotNull prepend="and"
property="tipoRfx">rfx.tipo_rfx = #tipoRfx# </isNotNull>LEFT OUTER JOIN rfx_cat_merc
ON rfx.id_rfx = rfx_cat_merc.id_rfx
 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>
        <isNotNull prepend="and" property="catMerc">rfx_cat_merc.id_cat_merc =
#catMerc# </isNotNull>
Thanks a lot for support!
Cheers, Valentina





________________________________
Da: meindert <meindert@eduflex.com>
A: user-java@ibatis.apache.org
Inviato: Lun 22 febbraio 2010, 15:13:08
Oggetto: RE: How to map a dynamic condition on Left Outer Join


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 withoutthe 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
 
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