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>