Oracle SQL statement - substitute null values


I'm using Oracle 9.2.

I would like to write an sql select statement that collects the ID and DATA fileds of a table and if the DATA filed contains NULL it gets the content of the DATA2 filed.  

So I would like to create one select statement instead of these two:

select id, data from mytable where data is not null;
select id, data2 from mytable where data is null;

Is it possible with one select statement?


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

do this:

select id, nvl(data, data2) from mytable;

or decode() function:

select id, decode(data, NULL, data2, data) from mytable;

Something like:

select id, decode(data, NULL, data, data2) as selected_data
from mytable;
Hi there is a function in 9i called NVL2. The syntax for this is NVL2(expr1, expr2, expr3). If expr1 is null then expr2 is returned. If expr1 is not null then expr3 is returned. You might use:

select id, nvl2(data, data2, data) from mytable;

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

seazodiac's solution is simple and will work.
fiftysixAuthor Commented:

thanks for the answers, but I still have problems.

This is my table:
  id - number
  data - varchar2
  data2 - long raw

When I use the nvl function I get the following error:

select id, nvl(data, data2) from mytable;
ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

when I use the decode function I get this:

select id, decode(data, NULL, data2, data) from mytable;
ORA-00997: illegal use of LONG datatype

Any idea?
try this one
because you are using a string data type and a long one in decode you should only use  same type of data type

select id, decode(data, NULL, to_char(data2), data) from mytable
fiftysixAuthor Commented:
to_char was my 1st idea also, but this is the result:

ORA-00932: inconsistent datatypes: expected CHAR got BINARY
If I'm not wrong Long raw is used to store graphics, sound, documents, or arrays of binary data and it cannot be selected using SQL*Plus .  That why you got error ORA-00932 .

I think solution is  create a table and convert the Long Raw data type to BLOB then select it as:

SQL>Create table mytable_2 (id number, data varchar2, data2 blob);
table created
SQL>insert into mytable_2  select id, data,  to_lob(data2) from mytable;

TO_LOB is used to covert Long Raw to BLOB (8.1x or higher)


SQL> select id, decode(data, NULL, to_char(data2), data) from mytable

Use the functions of the UTL_RAW package.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
imagine you have an indexe on data and it's a big table with decode you are going to make a full scan , is'nt it ???
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.