Display the records between two range I know the nvl function only allows the same data type(ie. number or char or date Nvl(comm, 0)), if commission is null then the text �Not Applicable� want to display, instead of blank space. How do I write the query

  • Interview Candidate
  • 13
  • 3025

Showing Answers 1 - 13 of 13 Answers

Satyavani

  • Jul 27th, 2005
 

you can use the decode function for the above requirement. Please find the query as below:  
 
select ename,decode(nvl(comm,0),0,'Not Applicable',comm) from scott.emp; 
 
Thanks, 
satyavani

Dr. Codd

  • Jun 16th, 2008
 

If "comm" is zero then the above query will give wrong result.
Query should be

SELECT NVL (To_Char(comm), 'Not Applicable') FROM table_name;

  Was this answer useful?  Yes

emraan

  • Dec 29th, 2009
 

1) SELECT e.*, nvl2(comm, 'Not Null', 'Null') FROM emp e  

2) SELECT e.*, decode(comm, '', 'Is Null', 'Is not Null') FROM emp e

  Was this answer useful?  Yes

katgeektalk

  • Aug 26th, 2010
 

If the values in the field comm both NULL and 0 the query should be

select empno, decode(nvl2(comm, to_char(comm),
'N/A'), '0', 'N/A')
from scott.emp

NVL2 to convert NULL value into N/A
Decode to convert 0 value into N/A

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions