Добрый вечер. Я тут вот с чем столкнулся: переводим отчеты с Oracle на SAP (СУБД Sybase). Поэтому возникла необходимость уйти от оракловых хранимых функций.
Вот такой запрос: (вникать во все не нужно, меня интересуют лишь хранимые функции kollecto.getlegalgroup_ce() и kollecto.getlegalbranch( )branch, которые нужно заменить на джоины, получив при этом тот же самый результат:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
select
1 cases,
gp. account_id,
to_char(a34.CREATION_DATE,'dd.mm.yyyy') "Дата направления запроса",
to_char(a35.CREATION_DATE,'dd.mm.yyyy') "Дата получения оригинала ПП",
kollecto.getlegalgroup_ce(a.ceid, a34.creation_date)legal_group,
kollecto.getlegalbranch(a.ceid, a34.creation_date)branch,
case when a34. account_id=a35. account_id then 1 else 0 end "Получили оригинал ПП",
lc. LAST_ACTION_CODE "Последний код",lc.ALL_CODES "Предыдущие отметки по кейсу"
FROM TVER.legal_state_duty_pay gp
JOIN kollecto.accounts a ON gp. account_id=a. account_id
JOIN KOLLECTO.legal_counter lc ON a. account_id=lc. account_id
JOIN
(select account_id, max(CREATION_DATE)CREATION_DATE
from (select ACCOUNT_ID, CREATION_DATE from KOLLECTO.ACCOUNT_COMMENTS where comment_id in (35749) /*A34-Направлен запрос в банк на получение оригинала ПП*/
union all
select ACCOUNT_ID,TRIAL_DATE from KOLLECTO.LEGAL_TRIAL where comment_id in (35749) /*A34-Направлен запрос в банк на получение оригинала ПП*/) group by account_id)A34
ON gp. account_id=a34. account_id
LEFT JOIN
(select account_id, max(CREATION_DATE)CREATION_DATE
from (select ACCOUNT_ID, CREATION_DATE from KOLLECTO.ACCOUNT_COMMENTS where comment_id in (35750) /* A35-Получен оригинал ПП от банка*/
union all
select ACCOUNT_ID,TRIAL_DATE from KOLLECTO.LEGAL_TRIAL where comment_id in (35750) /* A35-Получен оригинал ПП от банка*/) group by account_id)A35
ON a34. account_id=a35. account_id
Вот одна из 2х хранимых функций. Эта функция принимает 2 значения: p_ceid и p_date, возвращает v_legalgroup.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
create or replace function getlegalgroup_ce(p_ceid kollecto.collection_entities.ceid%type,
p_date kollecto.legal_executory.creation_date%type) return varchar2 as v_legalgroup kollecto.groups.name%type;
v_first_group kollecto.groups.name%type;
v_idusr kollecto.users.idusr%type;
begin
select distinct first_value(coj.id_new_owner)over(partition by coj.ceid order by coj.begin_date desc) into v_idusr
from tver.coj
where coj.ceid=p_ceid
and trunc(p_date) between trunc(coj.begin_date) and trunc(coj.end_date);
select (select distinct first_value(g.name)over(partition by cuj.idusr order by cuj.begin_date desc)
from tver.ce_usrgroup_jnl cuj,
kollecto.groups g
where cuj.idgrp=g.idgrp
and g.unique_flag=1
--and (g.name like 'Legal_EKAT%' or g.name like 'Legal_KALUGA%' or g.name like 'Legal_VOLGA%' or g.name like 'Legal_TVER%')
and (g.name like 'Legal%' or g.name like 'Field%')
and cuj.idusr=v_idusr
and trunc(p_date)<=trunc(cuj.begin_date)) into v_first_group
from dual;
select nvl((select distinct first_value(g.name)over(partition by cuj.idusr order by cuj.begin_date desc)
from tver.ce_usrgroup_jnl cuj,
kollecto.groups g
where cuj.idgrp=g.idgrp
and g.unique_flag=1
--and (g.name like 'Legal_EKAT%' or g.name like 'Legal_KALUGA%' or g.name like 'Legal_VOLGA%' or g.name like 'Legal_TVER%')
and (g.name like 'Legal%' or g.name like 'Field%')
and cuj.idusr=v_idusr
and trunc(p_date) between trunc(cuj.begin_date) and trunc(cuj.end_date)),v_first_group) into v_legalgroup
from dual;
return v_legalgroup;
end getlegalgroup_ce;
В общем, я тут голову уже сломал. Пытался сделать во FROM через вложенные подзапросы, но столкнулся с проблемой видимости v_idusr в другом подзапросе при расчете v_legalgroup, где на v_idusr связка идет.
Хотелось бы найти более-менее лаконичное решение, которое будет в дальнейшем использоваться вместо этой функции.
