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