ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Abeyratne, Sam" <sam.abeyra...@cingular.com>
Subject Can the <iterate> tag be used when the iteration is on a database name?
Date Wed, 13 Apr 2005 21:08:15 GMT
      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