?
Solved

Oracle SQL statement - substitute null values

Posted on 2003-11-11
11
Medium Priority
?
6,784 Views
Last Modified: 2007-12-19
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

0
Comment
Question by:fiftysix
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 9723159
do this:

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

or decode() function:

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

0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 9723253
Something like:

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

Expert Comment

by:johnster_uk
ID: 9723881
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;

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 3

Expert Comment

by:taisk
ID: 9726152
seazodiac's solution is simple and will work.
0
 

Author Comment

by:fiftysix
ID: 9729470
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?
0
 
LVL 8

Expert Comment

by:gajender_99
ID: 9730050
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
0
 

Author Comment

by:fiftysix
ID: 9730176
to_char was my 1st idea also, but this is the result:

ORA-00932: inconsistent datatypes: expected CHAR got BINARY
0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 9730539
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 .

0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 9730723
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


0
 

Accepted Solution

by:
fifty_ earned 90 total points
ID: 9787713
Use the functions of the UTL_RAW package.


fifty_
0
 

Expert Comment

by:AYEB
ID: 12994423
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 ???
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question