RE: Problem adding a join to a hierarchial query

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Wed, 17 Nov 2004 15:00:27 -0500
Message-ID: <003b01c4cce0$1537b710$2004a8c0@development.perceptron.com>

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_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 2:43 PM To: oracle-l_at_freelists.org
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_at_perceptron.com> wrote:
> So, is it true that each "root" in PROVIDER_CONTRACT_STATUS table has
> only one leaf?
>
>
>
> Igor Neyman, OCP DBA
> ineyman_at_perceptron.com

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


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

Received on Wed Nov 17 2004 - 14:02:27 CST