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
Solved

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

Posted on 2011-09-09
13
409 Views
Last Modified: 2012-05-12
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
Comment
Question by:XxtremePro
  • 8
  • 5
13 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 36513067

wrap emp name in NVL function



nvl(emp_name,'abcdef')
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 36513070
s elect emp_id, nvl(emp_name,'abcdef') emp_name from your_table
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36513075
you could also use coalesce


coalesce(emp_name,'abcdef')
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Closing Comment

by:XxtremePro
ID: 36513132
Just banged right on the head of the nail delivering the required answers... Great Answer Dude!!!
0
 

Author Comment

by:XxtremePro
ID: 36513264
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 36513305
nvl(trim(emp_name),'abcdef')
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36513358
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
 

Author Comment

by:XxtremePro
ID: 36513522
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 36513537
why did you repost that comment?
0
 

Author Comment

by:XxtremePro
ID: 36513548
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 36513573
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 36513593
oh I see it now,  you capitalized  "NO VALUE "

so, still the same request as above
0
 

Author Comment

by:XxtremePro
ID: 36513671
ok am really sorry.

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

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Title # Comments Views Activity
oracle- set role and grant privileges 6 38
form builder not starting 3 55
pl/sql - query very slow 26 71
Function to return one result based on data in first query 11 49
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 …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

839 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