Solved

Need to modify the output by replacing values in output records result of execution of SQL

Posted on 2011-09-09
9
346 Views
Last Modified: 2012-06-22
I have an SQL Query written.

It gives 70,000+ records. The problem is I do not know how to figure out to replace the value of one of the resulting columns whose values are blank and null with my custom value

ex:

output displayed is

Emp Id    Emp Name
---------    -------------

1                              << Null Value

2                 xyzabc

3                 abcxyz

4                            << Just a space and Not Null

5                poiuytr


In the above records 1 and 4 have no value( null and space ) associated and in the above ouput I want those employee names to be displayed as 'abcdef', so the output would be like


Emp Id    Emp Name
---------    -------------

1                abcdef

2                 xyzabc

3                 abcxyz

4                abcdef

5                poiuytr

I have used nvl method in my sql but still the null value is not replaced

Please calling for experts who can help me in successfully replacing null and blank space value with the value I would like to place 'abcdef' in the example
0
Comment
Question by:XxtremePro
[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
  • 6
  • 2
9 Comments
 
LVL 3

Expert Comment

by:evedder
ID: 36513779
something like this

select empid, decode(empname, nvl(empname, ''), 'abcdef'
                              '',               'abcdef'
                                , empname) empnamenonullsorblanks

sorry i can not testing it right now but is the idea.

with nvl you replace the null value in this case with a blank.
then with decode you are doing a kind of "if" so this means

if empname  is blank then output 'abcdef', else output empname value

0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 36513780
Did you try trim like I suggested in the last question?  I so, did it not work?
0
 

Author Comment

by:XxtremePro
ID: 36513803

** Error in the question I asked above**

In the above question posted there is a mistake in the line

I have used nvl method in my sql but still the 'space'  value needs to be replaced
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:XxtremePro
ID: 36513813
trim.... Let me see .. I did not quite pay attention to that part in the previous question.. Let me try and post the answer
0
 

Author Comment

by:XxtremePro
ID: 36513870
sdstuber.. whoever you are...

Your solutions work amazing... I am in praise of your glory..... wonderful...smack it boy...you got it..:)
0
 

Author Closing Comment

by:XxtremePro
ID: 36513880
This person sdstuber has got the knowledge which answered and matched my requirement

Kudos to him!!!
0
 

Author Comment

by:XxtremePro
ID: 36513888
sdstuber..

I had to traverse through the 70K records to get to see whether there are any null/empty values for emp_name

Can you suggest some easy way to test whether it has any such values and to make sure... Please let me know

Thanks in advance
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36514018
to find rows missing a name completely


select * from your_table
where emp_name is null


to find rows with a blank for a name

select * from your_table
where trim(emp_name) is null
and length(emp_name) > 0
0
 

Author Comment

by:XxtremePro
ID: 36514034
I was talking about finding rows in the result of the sql where some are null and some are blank

thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

763 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