NVL vs NVL2

NVL

  • It is used to Converts a null value to an actual value
  • Data types that can be used are date, character, and number
  • it accept two argument
  • both argument should be match in terms of data type.

i.e
–NVL(commission_pct,0)
–NVL(hire_date,’01-JAN-97′)
–NVL(job_id,’No Job Yet’)

 SELECT last_name,salary,commission_pct,
        salary + NVL(commission_pct, 0) "total sal"
 FROM employees

nvl

NVL2

  • It Accept three argument
  • It works like if-else statement

i.e
NVL2(commission_pct,’SAL+COMM’, ‘SAL’)

Here if commission_pct is not null then it will take ‘SAL+COM’ and If commission_pct is null then it will take ‘SAL’

SELECT last_name, salary, commission_pct,
NVL2(commission_pct,'SAL+COMM', 'SAL') income
FROM employees WHERE department_id =80;

nvl2

Leave a Reply

Your email address will not be published. Required fields are marked *