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

Need to change 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 value is blank with my custom value

ex output displayed is

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

1              

2                 xyzabc

3                 abcxyz

4                

5                poiuytr


In the above records 1 and 4 have no value 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

Please calling for experts who can help me in doing the same
0
XxtremePro
Asked:
XxtremePro
  • 8
  • 5
1 Solution
 
sdstuberCommented:

wrap emp name in NVL function



nvl(emp_name,'abcdef')
0
 
sdstuberCommented:
s elect emp_id, nvl(emp_name,'abcdef') emp_name from your_table
0
 
sdstuberCommented:
you could also use coalesce


coalesce(emp_name,'abcdef')
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
XxtremeProAuthor Commented:
Just banged right on the head of the nail delivering the required answers... Great Answer Dude!!!
0
 
XxtremeProAuthor Commented:
I used nvl function as told but I still see values which are empty so I think I need an even more better answer

I need to replace not just the null but also the empty values as a result of the output

For Ex -


ex:-

output displayed is

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

1              

2                 xyzabc

3                 abcxyz

4                             --- >> This value is a space and not Null

5                poiuytr


In the above records 1 associated with null and 4 have no value AS WELL AS          'NULL'  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

After accepting the solution and executing query I saw the output and had to put this comment... I hope to get a similar quick answer to this also as well from the experts...
0
 
sdstuberCommented:
nvl(trim(emp_name),'abcdef')
0
 
sdstuberCommented:
by the way,  it's more reliable (and appropriate) to click the "open a related question" link  when you need to add on new requirements.  especially when it's a closed question.


it's more reliable because when you open a related question,  all participants of the original are notified.  (in this case, me)  plus all other people that have notifications turned on for the zones of your new question.

it's more appropriate because one question is supposed to be, well, one question.
0
 
XxtremeProAuthor Commented:
I used nvl function as told but I still see values which are empty so I think I need an even more better answer

I need to replace not just the null but also the empty values as a result of the output

For Ex -


ex:-

output displayed is

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

1              

2                 xyzabc

3                 abcxyz

4                             --- >> This value is a space and not Null

5                poiuytr


In the above records 1 associated with null and 4 have NO VALUE AS WELL AS          'NULL'  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

After accepting the solution and executing query I saw the output and had to put this comment... I hope to get a similar quick answer to this also as well from the experts...
0
 
sdstuberCommented:
why did you repost that comment?
0
 
XxtremeProAuthor Commented:
To give more clarity with regards to the question that has been asked and

to also notify that the comment to repost is more clear explanation of my requirement
0
 
sdstuberCommented:
I don't understand.

if there is a difference between http:#36513264  and http:#36513522   I don't see it.

but,  as I said before.  If you are adding new requirements you should open a new question.
0
 
sdstuberCommented:
oh I see it now,  you capitalized  "NO VALUE "

so, still the same request as above
0
 
XxtremeProAuthor Commented:
ok am really sorry.

I am not used to the way of asking questions in Experts Exchange.. I would ask a related question

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now