Oracle - If one column is not null then make the other column blank-CodePudding

I have a simple table

create table employees (empno int primary key, empname varchar(30), 
emailaddress varchar(30), phonenumber varchar(10))

EMPNO   EMPNAME EMAILADDRESS    PHONENUMBER
1         Emma  [email protected]  82354566
2          Tom  [email protected]   984537665
3          Bob  [email protected]

I want to show the results such as if phone number for a particular customer is not blank, then make his email address as blank else display the email address as it is.

My desired result should be

EMPNO   EMPNAME EMAILADDRESS    PHONENUMBER
1         Emma                  82354566
2         Tom                   984537665
3         Bob   [email protected]

It should display blank and should not display not null.

I tried the following

select *
case 
when phonenumber is not null 
then '' as emailaddress
end
from employees

but it says ORA-00923: FROM keyword not found where expected

Here is my demo - DB Fiddle

CodePudding user response：

Change to:

select empno, empname, phonenumber,
case 
when phonenumber is not null 
then '' 
else emailaddress
end as emailaddress
from employees

DBFIDDLE

CodePudding user response：

As xQbert said, your case construct is wrong, but you are also missing a comma between * and your case expression, and if you combine * with anything else then it has to be prefixed with the table name or alias.

So you could do:

select e.*,
  case when phonenumber is not null then null else emailaddress end as etc
from employees e

Or slightly shorter (relying on a missing else evaluating to null anyway):

select e.*,
  case when phonenumber is null then emailaddress end as etc
from employees e

Or to not show the email address at all if there is a phone number:

select
  empno,
  empname,
  case when phonenumber is null then emailaddress end as emailaddress,
  phonenumber
from employees

EMPNO	EMPNAME	EMAILADDRESS	PHONENUMBER
1	Emma	null	82354566
2	Tom	null	984537665
3	Bob	[email protected]	null

Or if you only want to show one or the other as a single column:

select
  empno,
  empname,
  case when phonenumber is null then emailaddress else phonenumber end as etc
from employees

Or use coalesce instead of case:

select
  empno,
  empname,
  coalesce(phonenumber, emailaddress) as etc
from employees

EMPNO	EMPNAME	ETC
1	Emma	82354566
2	Tom	984537665
3	Bob	[email protected]

db<>fiddle