| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Problem adding a join to a hierarchial query
Create or replace joined_view
AS
Select P.provider_id, PC. status_id, PC. prev_id, PC.level
FROM (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) PC,
PROVIDER P
Igor Neyman, OCP DBA
ineyman_at_perceptron.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mike Spragg
Sent: Wednesday, November 17, 2004 10:58 AM
To: oracle-l_at_freelists.org
Subject: Problem adding a join to a hierarchial query
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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Nov 17 2004 - 10:37:46 CST
 |
 |