开发者

CTE, recursive search in 2 different tables

开发者 https://www.devze.com 2023-03-26 16:21 出处:网络
i have 2 queries go gather data from different tables, both recursive, but closely related. Both work well:

i have 2 queries go gather data from different tables, both recursive, but closely related.

Both work well:

First pulls subsidiaries:

with
relations as
(
select orgid as children,org_immediate_parent_orgid as orgid,'Sub' as relation
from oa.organizations 
)
select distinct relation, level, orgid, children
from relations
where children is not null
start with orgid in (identifier)
connect by
nocycle prior children = orgid
order by 2,3,4

Second affiliates:

with
relations as
(
select affiliated_orgid as children, orgid as orgid,'Aff' as relation
from oa.org_affiliations    
)
select distinct relation, level, orgid, children
from relations
where children is not null
start with orgid in (identifier)
connect by
nocycle prior children = orgid
order by 2,3,4

Recursion goes well in both, as i expected. Yes children and orgid are exchanged in queries, i would say subsidiaries is a "inverse relation" of affiliation (atleast in concept), but results are as expected

I want to run all together. Now i want the results of the first loop(for subs and aff) as input in the following loop(for subs and aff), till children are null. So if i get one result from each, i want both ids as input for each query.

I know i cant do inside "With As" the following.

select orgid as children,org_immediate_parent_orgid as orgid,'Sub' as relation
from oa.organizations

UNION ALL

select affiliated_orgid as children, orgid as orgid,'Aff' as relation
from oa.org_affiliations    
开发者_Go百科

What are my alternatives, solutions?

0

精彩评论

暂无评论...
验证码 换一张
取 消