0

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

What are my alternatives, solutions?

Cœur
  • 37,241
  • 25
  • 195
  • 267
blueomega
  • 281
  • 2
  • 15
  • Not immediately voting to close but your original question is much clearer to me. Why the new question? http://stackoverflow.com/questions/6954779/sql-multi-condition-cte-recursion – Lieven Keersmaekers Aug 09 '11 at 07:35
  • simply a more concise problem, dont know how to create 2 distinct queries inside a loop, join results and requery them. And till now no solutions present, as helpful as they were. A sample on how to do something similar is all i need. – blueomega Aug 09 '11 at 08:28

0 Answers0