RE: Problem adding a join to a hierarchial query

  • From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
  • To: <meltedpat@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 17 Nov 2004 14:33:24 -0500

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

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 12:26 PM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l


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

Other related posts: