did you try $[]$ ?
Brandon
On 4/13/05, Abeyratne, Sam <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] i,
>
> ( select distinct interface_name
>
> from [EMAIL PROTECTED]
>
> 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 [EMAIL PROTECTED] i,
>
> ( select distinct interface_name
>
> from [EMAIL PROTECTED]
>
> 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 [EMAIL PROTECTED] i,
>
> ( select distinct interface_name
>
> from [EMAIL PROTECTED]
>
> 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>
>
>
