NVL function helps you convert a null to an actual value.
Syntax:- NVL (expr1, expr2)
In the syntax:
expr1 is the source value or expression that may contain a null
expr2 is the target value for converting the null
Guidelines for NVL function:-
1) Data types that can be used are date, character and number.
2) Data types must match:
- NVL (commission, 0) here commission must be of NUMBER type
- NVL (hire_date, 11-JUL-14) here hire_date must be of DATE type
- NVL (job_title, Software Developer), here job_title must be of CHAR or VARCHAR2
Query:-
SELECT name, salary, NVL (commission, 0), ((salary*12) + (salary*12*NVL(commission, 0))) ANNUAL_SALARY FROM employees;
The above query calculates the annual salary of all employees by multiplying the monthly salary by 12 and then adding the commission to it.
NOTE: The annual compensation is calculated only for those employees who earn a commission.
If any column value in an expression is null, the result is null. To calculate values for all employees,
we convert the null value to a number before applying the arithmetic operator. In the above query
, the NVL function is used to convert null values to zero.
0 Comment(s)