RE: Problem adding a join to a hierarchial query

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Wed, 17 Nov 2004 14:33:24 -0500
Message-ID: <003a01c4ccdc$4dd7db80$2004a8c0@development.perceptron.com>

So, is it true that each "root" in PROVIDER_CONTRACT_STATUS table has only one leaf?

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 12:26 PM To: oracle-l_at_freelists.org
Subject: Re: Problem adding a join to a hierarchial query

Thanks everybody for the help!

By tweaking your suggestions I have come up with the following:

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

provider_id    curr_id          prev_id
323232        87944200     87825800
                   87825800
000001        87332200
006400        88249600    88173600
                   88173600    88169600
                   88169600

Is there any way to get the following result where the provider_id is populated on every row?

provider_id    curr_id          prev_id 
323232         87944200     87825800
323232         87825800
000001         87332200
006400         88249600    88173600
006400         88173600    88169600
006400         88169600

Thanks again - Mike
--

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

--

http://www.freelists.org/webpage/oracle-l Received on Wed Nov 17 2004 - 13:29:35 CST