create table test(emp_id varchar2(20), name varchar2(20), email varchar2(100));

insert into test values ('1','a','a@b.com');
insert into test values ('2',null,'a1@b.com');
insert into test values ('3',null,null);

commit;

Teh requirement is to print name if it is not null. If it is null then print email. If email is also null then print emp_id.

Following is what I tried:
select decode(decode(name,null,nvl(email,'xxx')),'xxx',emp_id) from test where emp_id='3';

SELECT test.*,
       CASE
          WHEN name IS NOT NULL THEN name
          WHEN name IS NULL AND email IS NOT NULL THEN email
          ELSE emp_id
       END
          AS new_name
  FROM test
--from OTN forum:  
  select test.*, coalesce(name,email, emp_id) from test

nvl(name, nvl(email,emp_id))