ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brandon Goodin <brandon.goo...@gmail.com>
Subject Re: Can the <iterate> tag be used when the iteration is on a database name?
Date Wed, 13 Apr 2005 21:24:31 GMT
did you try $[]$ ?

Brandon

On 4/13/05, Abeyratne, Sam <sam.abeyratne@cingular.com> wrote:
>  
>  
> 
>       Is there a way to iterate the database name using ibatis? Here is a
> sample sqlmap that I am using. But in this case I am not getting the
> database names to appear. I am getting '?' instead. I want the sql that is
> highlighted in yellow to iterate through multiple databases. Say for now,
> two databases called testdb1 and testdb2. I am passing a java.util.List
> containing a ListArray with 'testdb1' and 'testdb2.' This piece needs to be
> dynamic since we will be adding and removing multiple databases from the
> list. Any insight would be very much appreciated! 
> 
>   
> 
> Thanks! 
> 
> Sam 
> 
>   
> 
>       <select id="queryInterfaceBacklogs_DUAL"
> parameterClass="list" resultMap="interface_backlog_result"
> cacheModel="interface_backlog_cache"> 
> 
>       select 
> 
>       interface_name as SYSTEM_NAME, 
> 
>       sum(backlog_val) as BACKLOG_VAL, 
> 
>       sum(backlog_last1) as BACKLOG_LAST1, 
> 
>       sum(backlog_last2) as BACKLOG_LAST2, 
> 
>       sum(backlog_last3) as BACKLOG_LAST3 
> 
>       from 
> 
>       ( 
> 
>         <iterate open="(" close=")" conjunction="UNION ALL"> 
> 
>         select 
> 
>         a.interface_name, 
> 
>         sum(decode(i.BACKLOG_VAL,'',0,i.BACKLOG_VAL)) as BACKLOG_VAL, 
> 
>         sum(decode(i.BACKLOG_LAST1,'',0,i.BACKLOG_LAST1)) as BACKLOG_LAST1, 
> 
>         sum(decode(i.BACKLOG_LAST2,'',0,i.BACKLOG_LAST2)) as BACKLOG_LAST2, 
> 
>         sum(decode(i.BACKLOG_LAST3,'',0,i.BACKLOG_LAST3)) as BACKLOG_LAST3 
> 
>         from INTERFACE_STATUS@#[]# i, 
> 
>           ( select distinct interface_name 
> 
>           from interface@#[]# 
> 
>           where category='Provisioning' 
> 
>           )a 
> 
>         where SYSTEM_NAME is not null 
> 
>         and a.interface_name = i.system_name 
> 
>         group by a.interface_name 
> 
>         </iterate> 
> 
>   
> 
>         UNION ALL 
> 
>   
> 
>         select distinct a.interface_name, 0,0,0,0 
> 
>         from INTERFACE_STATUS i, 
> 
>           ( 
> 
>           select distinct interface_name 
> 
>           from interface 
> 
>           where category='Provisioning' 
> 
>           )a 
> 
>       ) 
> 
>       group by interface_name 
> 
>   </select> 
> 
>   
> 
> The final sql should look like: 
> 
>   
> 
>   
> 
>       <select id="queryInterfaceBacklogs_DUAL"
> parameterClass="list" resultMap="interface_backlog_result"
> cacheModel="interface_backlog_cache"> 
> 
>       select 
> 
>       interface_name as SYSTEM_NAME, 
> 
>       sum(backlog_val) as BACKLOG_VAL, 
> 
>       sum(backlog_last1) as BACKLOG_LAST1, 
> 
>       sum(backlog_last2) as BACKLOG_LAST2, 
> 
>       sum(backlog_last3) as BACKLOG_LAST3 
> 
>       from 
> 
>       ( 
> 
>         select 
> 
>         a.interface_name, 
> 
>         sum(decode(i.BACKLOG_VAL,'',0,i.BACKLOG_VAL)) as BACKLOG_VAL, 
> 
>         sum(decode(i.BACKLOG_LAST1,'',0,i.BACKLOG_LAST1)) as BACKLOG_LAST1, 
> 
>         sum(decode(i.BACKLOG_LAST2,'',0,i.BACKLOG_LAST2)) as BACKLOG_LAST2, 
> 
>         sum(decode(i.BACKLOG_LAST3,'',0,i.BACKLOG_LAST3)) as BACKLOG_LAST3 
> 
>         from INTERFACE_STATUS@testdb1 i, 
> 
>           ( select distinct interface_name 
> 
>           from interface@testdb1 
> 
>           where category='Provisioning' 
> 
>           )a 
> 
>         where SYSTEM_NAME is not null 
> 
>         and a.interface_name = i.system_name 
> 
>         group by a.interface_name 
> 
>   
> 
>        UNION ALL 
> 
>   
> 
>         select 
> 
>         a.interface_name, 
> 
>         sum(decode(i.BACKLOG_VAL,'',0,i.BACKLOG_VAL)) as BACKLOG_VAL, 
> 
>         sum(decode(i.BACKLOG_LAST1,'',0,i.BACKLOG_LAST1)) as BACKLOG_LAST1, 
> 
>         sum(decode(i.BACKLOG_LAST2,'',0,i.BACKLOG_LAST2)) as BACKLOG_LAST2, 
> 
>         sum(decode(i.BACKLOG_LAST3,'',0,i.BACKLOG_LAST3)) as BACKLOG_LAST3 
> 
>         from INTERFACE_STATUS@testdb2 i, 
> 
>           ( select distinct interface_name 
> 
>           from interface@testdb2 
> 
>           where category='Provisioning' 
> 
>           )a 
> 
>         where SYSTEM_NAME is not null 
> 
>         and a.interface_name = i.system_name 
> 
>         group by a.interface_name 
> 
>   
> 
>         UNION ALL 
> 
>   
> 
>         select distinct a.interface_name, 0,0,0,0 
> 
>         from INTERFACE_STATUS i, 
> 
>           ( 
> 
>           select distinct interface_name 
> 
>           from interface 
> 
>           where category='Provisioning' 
> 
>           )a 
> 
>       ) 
> 
>       group by interface_name 
> 
>   </select> 
> 
>

Mime
View raw message