fiftysix
asked on
Oracle SQL statement - substitute null values
Hi,
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?
thanks,
fiftysix
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?
thanks,
fiftysix
Something like:
select id, decode(data, NULL, data, data2) as selected_data
from mytable;
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;
select id, nvl2(data, data2, data) from mytable;
seazodiac's solution is simple and will work.
ASKER
hi,
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?
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
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
ASKER
to_char was my 1st idea also, but this is the result:
ORA-00932: inconsistent datatypes: expected CHAR got BINARY
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)
Then
SQL> select id, decode(data, NULL, to_char(data2), data) from mytable
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)
Then
SQL> select id, decode(data, NULL, to_char(data2), data) from mytable
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ???
select id, nvl(data, data2) from mytable;
or decode() function:
select id, decode(data, NULL, data2, data) from mytable;