PL SQL: нужно заменить хранимую функцию на JOIN. Как это оптимально сделать?

Question

[Дмитрий Митяев]

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;

Эта функция принимает 2 значения: p_ceid и p_date , возвращает v_legalgroup.

Как ее переписать на JOIN так, чтобы не было кучи вложенных запросов, т.к. в этом случае возникает проблема видимости v_idusr в другом подзапросе при расчете v_legalgroup, где на v_idusr связка идет.

Хотелось бы найти лаконичное решение, которое будет в дальнейшем использоваться вместо этой функции.

Answer 1

[Артем Черепахин]

Добрый день, Дмитрий Митяев.
Если я правильно понял проблему, то можно воспользоваться cte и join.

with v_idusr_tbl as 
(
select distinct first_value(coj.id_new_owner)over(partition by coj.ceid order by coj.begin_date desc) id_new_owner
 from tver.coj
where coj.ceid=p_ceid
  and trunc(p_date) between trunc(coj.begin_date) and trunc(coj.end_date)
)
, tbl_second_group as (
select distinct first_value(g.name)over(partition by cuj.idusr order by cuj.begin_date desc) name
, cuj.idusr
             from tver.ce_usrgroup_jnl cuj,
                  kollecto.groups g,
                  v_idusr_tbl q
            where cuj.idgrp=g.idgrp
              and g.unique_flag=1
              and (g.name like 'Legal%' or g.name like 'Field%')
              and cuj.idusr=q.id_new_owner
              and trunc(p_date) between trunc(cuj.begin_date) and trunc(cuj.end_date)
)
, tbl_first_group as (
select distinct first_value(g.name)over(partition by cuj.idusr order by cuj.begin_date desc) name
         from tver.ce_usrgroup_jnl cuj,
              kollecto.groups g,
              v_idusr_tbl q
        where cuj.idgrp=g.idgrp
          and g.unique_flag=1
          and (g.name like 'Legal%' or g.name like 'Field%')
          and cuj.idusr=q.id_new_owner
          and trunc(p_date)<=trunc(cuj.begin_date)
          and 0 = (select count(1) from tbl_second_group) /*Если в tbl_second_group ничего нет, то выбираем. 
          Скорее всего, это условие будет выполнено в конце при выполнении запроса, я не придумал способ засунуть в секцию from или exists, чтобы он вообще не выполнялся в случае непустого tbl_second_group*/
)

, tbl_legal_group as (
select name from tbl_second_group 
union all
select name from tbl_first_group 
)
select name into v_legalgroup from tbl_legal_group /*Вставляем в переменнную*/