Есть процедура:
with t as
(
select 0 ord,a.*
from actions a
where sysdate between sd and ed
and id = :act_id and md_hash = nvl(:action, md_hash)
union all
select 1,a.*
from actions a
where :action is null
and id != nvl(:act_id,0)
and sysdate between sd and ed
and lower(id||name) like ('%' || lower(:filter_name) || '%')
and act_sd <= nvl(to_date(:filter_ed, 'dd.mm.yyyy')+1-1/86400, act_sd)
and act_ed >= nvl(to_date(:filter_sd, 'dd.mm.yyyy'), act_ed)
and (
(act_ed < sysdate and :filter_closed = 1) or
(act_sd > sysdate and :filter_planned = 1) or
(sysdate between act_sd and act_ed and :filter_opened = 1))
),
r as(
select max(r.time_zone) - ibt_actions.get_db_time_zone as max_tz,
min(r.time_zone) - ibt_actions.get_db_time_zone as min_tz,
act_id
from action_regions ar,
regions r,
t
where ar.act_id =
t.id and sysdate between
ar.sd and ar.ed
and
r.id = ar.reg_id
and sysdate between
r.sd and r.ed
group by act_id
)
select
case
when act_sd = ibt.get_end_date then 'reserved'
when sysdate+nvl(min_tz,to_dsinterval('0 00:00:00')) > act_ed then 'closed'
when sysdate+nvl(max_tz,to_dsinterval('0 00:00:00')) > act_sd then 'started'
else 'created'
end as class,max_tz,min_tz,
t.*
from t,r
where
t.id = r.act_id(+)
order by ord,id desc
что бы ее выполнить, мне нужно подставить туда значение. Если есть значение, то приходит ответ
таблица:
CLASS|MAX_TZ|MIN_TZ|ORD|ID|NAME|ACT_SD|ACT_ED|DSC|PRIORITY|ONLY_THIS|STATE|SD|SU|ED|EU|MD_HASH|ACT_MEMBERS|
Итог: мне нужно получить не один ответ, а все че есть в базе поэтой процедуре. (изменять запросы я не могу)