ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Celaya, Tina \(GE Indust, ConsInd\)" <tina.cel...@ge.com>
Subject RE: Problem Using Iterate
Date Thu, 12 Oct 2006 17:37:45 GMT
Ok i´ll try your recomendations i´m sure it will work that why, if it doesnt work i´ll let
you know, ;) 
thank you guys very much
tina

-----Original Message-----
From: Poitras Christian [mailto:Christian.Poitras@ircm.qc.ca]
Sent: Jueves, 12 de Octubre de 2006 09:12 a.m.
To: user-java@ibatis.apache.org
Subject: RE: Problem Using Iterate


You should also use the property attribute in your iterate tag (see iBATIS doc).
It is likely that you will need '$' instead of '#' to make it work.
 
<select id="getSVMarketCC" parameterClass=" java.util.Map" resultClass="salesVariance">
          select
     typeName,
     sum(totalUnits) units,
     sum(totalAmount) sales,
     sum(totalProvision) rebates

     <iterate property="list1" prepend="," open="" close="" conjunction=",">
       $[]$
     </iterate>

     from (
     select
     p.pl pline, c.cliente customer,  <http://p.pg/> p.pg productGroup
     case when c.nacional=2 then case when c.geco = 1 then 'EXPO INT'
     else case when c.geco = 2 then 'EXPO ROW' end end else 'DOMESTIC' end typeName,
     sum( isnull( cc.cantidad,0)*isnull(p.pack,1)-case when cc.transaccion='CM'
     and cc.o_tipo_tran in ('GECM-OTHER-MXP-CM','GECM-OTHER-USD-CM') and cc.cantidad>0
     and cc.subtotal&lt;0 and cc.preciounitario&lt;0 then cc.cantidad else 0 end)
totalUnits,
     sum((cc.subtotal-(isnull(cc.provision,0)))* case when moneda='USD' then #tc# else 1 end)
totalAmount,
     sum(isnull(cc.provision,0)* case when moneda='USD' then #tc# else 1 end) totalProvision

     from incentivos..carteraclientes cc
     left outer join om..productos p on cc.producto=p.oid
     left outer join om..clientes c on cc.cliente=c.oid
     where cc.transaccion in ('INV','ADJ','CM','DM')
     and cc.mes &lt; #mesFin#
     and cc.mes $gt; #mesIni#
     and cc.ano = #anio#
     group by
     c.nacional,c.geco,p.pl, c.cliente,  <http://p.pg/> p.pg) a
     group by typeName

     <iterate property="list1" prepend="," open="" close="" conjunction=","> 
       $[]$
     </iterate>
</select>

Christian
 

  _____  

From: Jeff Butler [mailto:jeffgbutler@gmail.com] 
Sent: Wednesday, 11 October 2006 17:29
To: user-java@ibatis.apache.org
Subject: Re: Problem Using Iterate


One problem is that your CDATA tags are causing the parser to ignore the nested <iterate>
tags.  It is, in general, a bad practice to surround an entire SQL statement with CDATA tags.
 
You should only use CDATA to escape specific characters in your SQL map or, better, use the
entities like &gt; &lt;
 
See here for information about CDATA:
 
http://www.w3schools.com/xml/xml_cdata.asp
 
Jeff Butler


 
On 10/11/06, Celaya, Tina (GE Indust, ConsInd) < tina.celaya@ge.com> wrote: 

Hello, i want to use the iterator tag and dinamicaly create the select and the groupby list
for the colums i want to get I have this query now 

<select id="getSVMarketCC" parameterClass="java.util.Map" resultClass="salesVariance">
     <![CDATA[
     select
     typeName,
     productGroup,
     pline, 
     customer,
     sum(totalUnits) units,
     sum(totalAmount) sales,
     sum(totalProvision) rebates
     from (
     select
     p.pl pline, c.cliente customer, p.pg productGroup 
     case when c.nacional=2 then case when c.geco = 1 then 'EXPO INT'
     else case when c.geco = 2 then 'EXPO ROW' end end else 'DOMESTIC' end typeName,
     sum( isnull(cc.cantidad,0)*isnull(p.pack,1)-case when cc.transaccion='CM'
     and cc.o_tipo_tran in ('GECM-OTHER-MXP-CM','GECM-OTHER-USD-CM') and cc.cantidad>0
     and cc.subtotal<0 and cc.preciounitario<0 then cc.cantidad else 0 end) totalUnits,
     sum(( cc.subtotal-(isnull(cc.provision,0)))* case when moneda='USD' then #tc# else 1
end) totalAmount,
     sum(isnull(cc.provision,0)* case when moneda='USD' then #tc# else 1 end) totalProvision
     from incentivos..carteraclientes cc 
     left outer join om..productos p on cc.producto=p.oid
     left outer join om..clientes c on cc.cliente=c.oid
     where cc.transaccion in ('INV','ADJ','CM','DM')
     and cc.mes < #mesFin#
     and cc.mes > #mesIni#
     and cc.ano = #anio#
     group by
     c.nacional,c.geco,p.pl, c.cliente, p.pg) a
     group by typeName, productGroup, pline,customer
     ]]>
   </select> 

And i sent this hashmap as the parameterClass
hm.put("mesFin",new Integer(month1))
hm.put("mesIni",new Integer(month2))
hm.put("tc",new Double (exchType))
hm.put("anio",new Integer(year)) 

What i want now is to add a List containing the strings "productGroup","pline","customer"
hm.put("list1",list) and do this:

<select id="getSVMarketCC" parameterClass=" java.util.Map" resultClass="salesVariance">
     <![CDATA[
     select
     typeName,
     sum(totalUnits) units,
     sum(totalAmount) sales,
     sum(totalProvision) rebates

     <iterate prepend="," open="" close="" conjunction=",">
       #list1[]#
     </iterate>

     from (
     select
     p.pl pline, c.cliente customer, p.pg productGroup
     case when c.nacional=2 then case when c.geco = 1 then 'EXPO INT'
     else case when c.geco = 2 then 'EXPO ROW' end end else 'DOMESTIC' end typeName,
     sum( isnull( cc.cantidad,0)*isnull(p.pack,1)-case when cc.transaccion='CM'
     and cc.o_tipo_tran in ('GECM-OTHER-MXP-CM','GECM-OTHER-USD-CM') and cc.cantidad>0
     and cc.subtotal<0 and cc.preciounitario<0 then cc.cantidad else 0 end) totalUnits,
     sum((cc.subtotal-(isnull(cc.provision,0)))* case when moneda='USD' then #tc# else 1 end)
totalAmount,
     sum(isnull(cc.provision,0)* case when moneda='USD' then #tc# else 1 end) totalProvision

     from incentivos..carteraclientes cc
     left outer join om..productos p on cc.producto=p.oid
     left outer join om..clientes c on cc.cliente=c.oid
     where cc.transaccion in ('INV','ADJ','CM','DM')
     and cc.mes < #mesFin#
     and cc.mes > #mesIni#
     and cc.ano = #anio#
     group by
     c.nacional,c.geco,p.pl, c.cliente, p.pg) a
     group by typeName

     <iterate prepend="," open="" close="" conjunction=","> 
       #list1[]#
     </iterate>
     ]]>
</select>

But i´m getting this error
--- Check the getSVMarketCC.
--- Check the parameter map.
--- Cause: com.ibatis.common.beans.ProbeException : Error getting ordinal list from JavaBean.
Cause java.lang.NumberFormatException: For input string: ""
Caused by: java.lang.NumberFormatException: For input string: ""
Caused by: com.ibatis.common.beans.ProbeException : Error getting ordinal list from JavaBean.
Cause java.lang.NumberFormatException: For input string: ""
Caused by: java.lang.NumberFormatException: For input string: ""

can anyone help me?



Tina Celaya
GE Consumer & Industrial
Systems Analyst

T +52 81 8318 5306
F +52 81 8318 5692
D *241 5306
E tina.celaya@ge.com
www.geindustrial.com.mx

Av. Churubusco 3900 Nte.
Col Industrial B. Juarez
Monterrey NL CP 64517
GE Equipo de Control y Distribucion S. de RL de CV






Mime
View raw message