Jun 15 2011

NVL, NVL2, COALESCE and NULLIF Function in Oracle

Category: SQLFatih Acar @ 03:39

These functions work with any data type and pertain to the use of null values in the expression list. These functions use of null value and returns result value.

NVL : Converts null value to an actual value.
NVL2 : If first expression is not null, return second expression. If first expression is null, return third expression. the first expression can have any data type.
COALESCE : Return first not null expression in the expression list.
NULLIF : Compares two expressions and returns null if they are equal,returns the first expression if they are not equal.

Example

Sample Table : employees

ID SALARY COMMISSION
1 10000 0.05
2 8000 null
3 5000 0.1

select ID, SALARY, (NVL(COMMISSION,1)*SALARY)+SALARY as COM_SALARY from employees;

Result :

ID SALARY COM_SALARY
1 10000 10500
2 8000 8000
3 5000 5500

If you do not use NVL function, when COM_SALARY have a null value, print null.

161,651 total views, 75 views today

facebook comments:

Leave a Reply

Time limit is exhausted. Please reload CAPTCHA.