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?
精彩评论