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
  • 7
  • 2981

Showing Answers 1 - 7 of 7 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