Oracle NVL function

Oracle NVL function

         NVL function replaces null value with same type data,

for ex:  If employee has no comm , instead of displaying NULL , u can display string like 'No Commission'

Display Employees getting no comm as zero.
SQL> select ename,nvl(comm,0) from emp;
ENAME      NVL(COMM,0)

---------- ----------------------------------------

SMITH      0

ALLEN      300

WARD       500

JONES      0

MARTIN     1400

BLAKE      0

CLARK      0

SCOTT      0

KING       0

TURNER     0

ADAMS      0


JAMES      0

FORD       0

MILLER     0

14 rows selected.

.
Query:

SQL> select ename,nvl(to_char(comm),'no comm') from emp;

ENAME      NVL(TO_CHAR(COMM),'NOCOMM')
---------- ----------------------------------------
SMITH      no comm
ALLEN      300
WARD       500
JONES      no comm
MARTIN     1400
BLAKE      no comm
CLARK      no comm
SCOTT      no comm
KING       no comm
TURNER     0
ADAMS      no comm

ENAME      NVL(TO_CHAR(COMM),'NOCOMM')
---------- ----------------------------------------
JAMES      no comm
FORD       no comm
MILLER     no comm

14 rows selected.

Ex2:  Get total salary of each department and Grand Total of employees,title total sum as "Grand Total".

SQL> select nvl(to_char(deptno),'Grand Total'), sum(sal) from emp group by  rollup(deptno);

O/P:

NVL(TO_CHAR(DEPTNO),'GRANDTOTAL')          SUM(SAL)
---------------------------------------- ----------
10                                             8750
20                                            10875
30                                             9400
Grand Total                                   29025


Note: In this example, we grouping by department and then calculating sum(sal) for each department and then applying rollup to produce grand total.

If you don't use  NVL,  Grand Total will be null value. So NVL replacing null values with String.

Tags:Oracle NVL function,Oracle 11g NVL function, Replace Null values with String using NVL,Using NVL in Oracle 11g.