Re: want some help on oracle outer join

alokh wrote
> My prob is to join two tables and depending on some value
> I have to chose the value of the field from these two outer join
> tables .I want some help regarding this

Oracle returns null values if it cannot find a match for the outer join. So, you may use nvl(..) or decode(..) to decide to show another column if the outer join fails:

    select t1.ordernr
    , nvl( t2.name, t1.customer_id )
    , decode( t2.name, null, 'Unkown', 'Known') || ' customer id'     from orders t1
    , customers t2
    where t1.customer_id = t2.customer_id (+);

to show the customer id if no matching customer name was found in table customers.

Arjan. Received on Sat Apr 24 1999 - 06:39:34 CDT