Re: HELP - NVL or DECODE

From: Paul Scott <pscott_at_tcp.co.uk>
Date: Thu, 09 Nov 2000 01:11:28 +0000
Message-ID: <lcuj0t0qkk33fkb1cshlqjhjolc034lbtg@4ax.com>

On Wed, 8 Nov 2000 16:49:16 +0200, "zak" <zak_at_intrafunk.com> wrote:

>I have the three following tables
>
>1. people
> per_id
> per_email
> per_tel
> per_addr
>2. organizations
> org_id
> org_email
> org_tel
> org_addr
>3. employees (this is for the many-to-many relations between the people &
>organizations table)
> per_id
> org_id
>
>select peo.per_email, peo.per_tel, peo.per_addr from people peo,
>organizations org, employees emp
>where peo.per_id=emp.emp_per_id(+)
>and emp.emp_org_id=org.org_id(+)
>
>lets assume for the example that there is a person for every organization
>
>What i want is if peo.per_email is null then give me the value of
>org.org_email,
>the same for peo.per_tel and peo.per_addr, in other words if a person's
>email, tel or address is not registered than have the corresponding details
>for his company.
>
>thanks in advance
>zak
>

try

select

  nvl(peo.per_email, org.org_email) email, 
  nvl(peo.per_tel, org.org_tel) tel,
  nvl(peo.per_addr, org.org_addr) addr

from
  people peo,
  organizations org,
  employees emp
where
  peo.per_id=emp.emp_per_id(+) and
  emp.emp_org_id=org.org_id(+)

Paul Scott
aspscott_at_tcp.co.uk
^^ remove 'as' Anti-Spam Received on Wed Nov 08 2000 - 19:11:28 CST