Larry
Hey All,
Here is the scenario. My sql has an iterate tag which iterates a select statement based on a list of sid values I am passing. In addition, I want to pass another variable. I tried a few options such as passing a ParameterMap, extra ParameterClass, etc, but haven't had any luck. I have enclosed an example SQL for reference. The '$[]$' will take a list of sids. I want to be able to specify another value in the where clause (i.e., ?data?). The iterate with the single list works fine. I am having issues when I try to introduce another variable.
Any insight would be much appreciated.
Thanks!
Sam
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=?data?
)a
where SYSTEM_NAME is not null
and a.interface_name = i.system_name
group by a.interface_name
</iterate>
)
group by interface_name
