You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Valentina Fabrizi <fa...@yahoo.it> on 2010/02/22 12:22:30 UTC
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 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_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
Thanks!
Cheers, ValentinaLEFT 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>????</select>select id="findCruscottoRfxDateCatMerc" cacheModel="cruscottoRfxDateCatMercCache" resultMap="cruscottoRfxResult" parameterClass="java.util.Map">
Re: How to map a dynamic condition on Left Outer Join
Posted by Fernando Rengifo <fr...@gmail.com>.
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 <me...@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
How to map a dynamic condition on Left Outer Join
Posted by Valentina Fabrizi <fa...@yahoo.it>.
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 <me...@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 <me...@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
Re: How to map a dynamic condition on Left Outer Join
Posted by Valentina Fabrizi <fa...@yahoo.it>.
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 <me...@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 <me...@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
RE: How to map a dynamic condition on Left Outer Join
Posted by meindert <me...@eduflex.com>.
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 <me...@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
Re: How to map a dynamic condition on Left Outer Join
Posted by Valentina Fabrizi <fa...@yahoo.it>.
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 <me...@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
RE: How to map a dynamic condition on Left Outer Join
Posted by meindert <me...@eduflex.com>.
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