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
WHERE P. provider_contract_status_id = PC.status_id

Igor Neyman, OCP DBA
ineyman@xxxxxxxxxxxxxx



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mike Spragg
Sent: Wednesday, November 17, 2004 10:58 AM
To: oracle-l@xxxxxxxxxxxxx
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

Other related posts: