RE: Problem adding a join to a hierarchial query

Try this (get "root" in in-line view and use to join with provider
table):

select p.provider_id, pc.provider_contract_status_id,
pc.prev_prov_contract_status_id  from 
(select CONNECT_BY_ROOT root
           provider_contract_status_id curr_id,
          prev_prov_contract_status_id prev_id,
          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.root;

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 2:43 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Problem adding a join to a hierarchial query

No - Each "root" in the PROVIDER_CONTRACT_STATUS table can have 0 to
many "leafs".

I think the problem I have is that only the current
provider_contract_status_id (or "root") has a tie back to get the
provider_id from the PROVIDER table.  What I am trying to do is apply
that associated provider_id to all the "leafs" that I get from the
"root".  I can tie it to the "root" but all "leafs" have a NULL
provider_id.

Hope that makes some sense.


On Wed, 17 Nov 2004 14:33:24 -0500, Igor Neyman <ineyman@xxxxxxxxxxxxxx>
wrote:
> So, is it true that each "root" in PROVIDER_CONTRACT_STATUS table has
> only one leaf?
> 
> 
> 
> Igor Neyman, OCP DBA
> ineyman@xxxxxxxxxxxxxx
--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Other related posts: