--nvl function
--used to replace null value with some value
SQL> select * from cust;
CUSTNUM CUSTNAME AGE PCODE SAL
---------- --------------- ---------- ---------- ----------
1 umesh 30 416502 10000
2 manisha 30 416501 15000
3 megha 25 416608 11000
4 pradip 36 416609 13000
5 niranjan 24 416508 23000
6 songonda 40 416505 53000
8 sheetal 30 50000
9 poo 25 416505 8000
8 rows selected.
SQL> select nvl(pcode,323000) from cust;
NVL(PCODE,323000)
-----------------
416502
416501
416608
416609
416508
416505
323000
416505
8 rows selected.
--nvl2 function
--used to replace not null and null values with some value
SQL> select nvl2(pcode,pcode,323000) from cust;
NVL2(PCODE,PCODE,323000)
------------------------
416502
416501
416608
416609
416508
416505
323000
416505
8 rows selected.
--nullif function
SQL> select nullif(pcode,pcode) from cust;
--if both the values are equal it returns null or else it returns left value
NULLIF(PCODE,PCODE)
-------------------
8 rows selected.
--nullif function
SQL> select nullif(pcode,123456) from cust;
NULLIF(PCODE,123456)
--------------------
416502
416501
416608
416609
416508
416505
416505
8 rows selected.
--coalesce function
--it takes minimum 2 arguments and returns the first not null from the table
records.
SQL> select * from cust;
CUSTNUM CUSTNAME AGE PCODE SAL
---------- --------------- ---------- ---------- ----------
1 umesh 30 416502 10000
2 manisha 30 416501 15000
3 megha 25 416608 11000
4 pradip 36 416609 13000
5 niranjan 24 416508 23000
6 songonda 40 416505 53000
8 sheetal 30 50000
9 poo 25 416505 8000
10 manasi 25
9 rows selected.
SQL> select coalesce(pcode,age) from cust;
COALESCE(PCODE,AGE)
-------------------
416502
416501
416608
416609
416508
416505
30
416505
25
9 rows selected.
SQL> select coalesce(age,pcode) from cust;
COALESCE(AGE,PCODE)
-------------------
30
30
25
36
24
40
30
25
25
9 rows selected.
