Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
  • NVL fuction in Oracle

    • 0
    • 1
    • 1
    • 1
    • 0
    • 0
    • 0
    • 0
    • 776
    Comment on it

    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


    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)

Sign In

Sign up using

Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: