GENERAL FUNCTION OR NULL FUNCTION

GENERAL FUNCTION OR NULL FUNCTION

//Mainly these functions are used to process the null values.

NVL()
NVL2()
NULLIF()
COALESCE()

NVL() : Its accept two arguments if the first arguments is null and its display the second arguments else its display the first arguments.

consider one example

select first_name,commission_pct,nvl(commission_pct,1)from employees;

Note: Here nvl(commission_pct,1) there is two arguments passed if commission_pct is null means it Display 1 otherwise its display first arguments that means commission_pct value.

NVL2( ): Its accept three arguments if the first arguments is null and its display the third arguments else its display the second arguments.

select first_name,commission_pct,nvl2(commission_pct,1,3)from employees;

Note: Here nvl2(commission_pct,1,3) there is three arguments passed if commission_pct is null means it Display 3rd otherwise its display second arguments value.

Null If (): It accepts two arguments return null  if both arguments are equal else it will display the first arguments.


SQL> select nullif(5,10) from dual;

NULLIF(5,10)
------------
           5

SQL> select nullif(15,10) from dual;

NULLIF(15,10)
-------------
           15

SQL> select nullif(15,15) from dual;

NULLIF(15,15)
-------------
             -

COALESCE():  It accept N Number of arguments returns the first not null values from the expression list. and Instead of NVL() we can use the COALESCE() .

select first_name,commission_pct,coalesce(commission_pct,null,null,1,null)from employees;

select first_name,commission_pct,coalesce(commission_pct,null,null,1,null)from employees;

Note: ORA-00909: invalid number of arguments