Problem adding a join to a hierarchial query
- From: Mike Spragg <meltedpat@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 17 Nov 2004 09:57:53 -0600
I am having problems with a hierarchial query and I am hoping someone
can point me in the right direction.
I am running Oracle 9i and I am trying to create a hierarchial query
with a join to get a related id in the result set. I can do the
hierarchial part no problem, however when I try to introduce the id
from the related table everything falls apart.
I have 2 tables with the following columns (I have left out unneeded
columns for clarity):
PROVIDER
provider_id
provider_contract_status_id
PROVIDER_CONTRACT_STATUS
provider_contract_status_id
prev_prov_contract_status_id
The PROVIDER table has the most current contract status id and there
is a FK relationshiop between PROVIER & PROVIDER_CONTRACT_STATUS based
on the provider_contract_status_id. I can easily show the hierarchy
in the PROVIDER_CONTRACT_STATUS table as follows:
select
provider_contract_status_id status_id,
prev_prov_contract_status_id prev_id,
level
from
provider_contract_status
start with provider_contract_status_id in (select
provider_contract_status_id from provider)
connect by prior prev_prov_contract_status_id = provider_contract_status_id;
status_id prev_id level
87944200 87825800 1
87825800 2
87332200 1
88249600 88173600 1
88173600 88169600 2
88169600 3
I have been asked to create a view showing the above hierarchy as well
as the corresponding provider_id from the PROVIDER table, like the
following:
provider_id curr_id prev_id level
323232 87944200 87825800 1
323232 87825800 2
000001 87332200 1
006400 88249600 88173600 1
006400 88173600 88169600 2
006400 88169600 3
I have tried different variations of adding the PROVIDER table to the
from and adding a where clause to no avail.
Can someone point me in the right direction as to how I might be able
to accomplish this?
Thanks - Mike
--
//www.freelists.org/webpage/oracle-l
Other related posts:
