10.NVL Function

NVL() :- This will be used to assign/display a value when there is a null value

SQL> SELECT * FROM EMP;

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY

---

   115 CHANDU          25000 manager    10-MAR-12        222
   105 kumar           12200                             555
   111 RAM             12000 A          12-DEC-14        222
   112 RAMESH          25000 B          29-DEC-14        333
   113 KRISHNA         30000 C          03-JAN-14        555
   114 SHEKAR          56000 manager    01-JAN-14        555

6 rows selected.

SQL> select job,nvl(job,’ANALYST’) FROM EMP;

JOB NVL(JOB,’A

---

manager manager
ANALYST
A A
B B
C C
manager manager

SQL> select * from emp;

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY

---

   115 CHANDU          25000 manager    10-MAR-12        222
   105 kumar           12200                             555
   111 RAM             12000 A          12-DEC-14        222
   112 RAMESH          25000 B          29-DEC-14        333
   113 KRISHNA         30000 C          03-JAN-14        555
   114 SHEKAR          56000 manager    01-JAN-14        555

View null records

SQL> SELECT * FROM EMP_NEW WHERE JOB=NULL; — = will not work in where condition

no rows selected

SQL> SELECT * FROM EMP_NEW WHERE JOB IS NULL; — IS will work in where condition

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY               MGR        TAX

---

   105 kumar           12200                             555

Share this post: on Twitter on Facebook on Google+