• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

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

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
XxtremePro
Asked:
XxtremePro
  • 6
  • 2
1 Solution
 
evedderCommented:
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
 
sdstuberCommented:
Did you try trim like I suggested in the last question?  I so, did it not work?
0
 
XxtremeProAuthor Commented:

** 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
XxtremeProAuthor Commented:
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
 
XxtremeProAuthor Commented:
sdstuber.. whoever you are...

Your solutions work amazing... I am in praise of your glory..... wonderful...smack it boy...you got it..:)
0
 
XxtremeProAuthor Commented:
This person sdstuber has got the knowledge which answered and matched my requirement

Kudos to him!!!
0
 
XxtremeProAuthor Commented:
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
 
sdstuberCommented:
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
 
XxtremeProAuthor Commented:
I was talking about finding rows in the result of the sql where some are null and some are blank

thanks
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now