Re: Problem adding a join to a hierarchial query

Hmm.  See below.

>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
>
>  
>
select
    p.provider_id,
    pcs.provider_contract_status_id curr_id,
    pcs.prev_prod_contract_status_id prev_id,
    level
from
    provider left join provider_contract_status on 
p.provider_contract_status_id = pcs.provider_contract_status_id
    connect by prior pcs.prev_prod_contract_status_id = 
pcs.provider_contract_status_id

This should work on 9i, no?

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

Other related posts: