Solved

Pick not null names

Posted on 2014-01-09
4
370 Views
Last Modified: 2014-01-13
How can i do this.


Name1 Name2 Name3  Name4

ABC          CDE    BBB

                                          KKK

                            MBC        LLL


So pick UP First non null name staring from Name 1,2,3,4

Final name
ABC
KKK
MBC
0
Comment
Question by:sam2929
  • 2
4 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39770082
Check out coalesce:

select coalesce(name1,name2,name3,name4) from sometable;
0
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 39770098
Use
...
where nvl(Name1,'')<>'' or nvl(Name2,'')<>'' ...

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39770104
HuaMinChen,

Your post doesn't make sense.

First piece that is wrong is that NVL(name1,'') doesn't do anything. An empty string in Oracle is considered a null so what that says is that if name1 is null, return null.

Second:
Given the data provided, please explain how your post will produce the expected results posted of:
Final name
ABC
KKK
MBC
0
 
LVL 32

Expert Comment

by:awking00
ID: 39771255
Coalesce was designed precisely for this kind of query. It simply says return the first non-null value reached (null if all values are null). You should close this question and award slightwv the points without delay.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

792 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