Oracle nvl function

Handling NULL values:

NULL: 

  • It is a value which is Unavailable / Unassigned / Unknown / Inapplicable.
  • A NULL is not the same as “Zero” or a “Blank space”.
  • If a row lacks the data for a particular column than that value is said to be NULL.
  • If any column value in an arithmetic expression is NULL, the overall result is also NULL.

Example:

    SQL> SELECT EName, Sal, Comm, Sal+Comm   2  FROM emp; ENAME  SAL    COMM   SAL+COMM ———- ———- ———- ———- SMITH  800 ALLEN 1600     300       1900 WARD 1250     500       1750 JONES 2975 MARTIN 1250    1400       2650 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 TURNER 1500       0       1500 ADAMS 1100 JAMES  950 FORD 3000 MILLER 1300 14 rows selected.

  • The above situation is called as NULL Propagation and has to be handled carefully using functions like NVL, NVL2 etc.

NVL Function:

The NVL function is used to convert a NULL value to an actual value.

Syntax:       NVL(Expr1, Expr2)

  • Expr1: It is the source value or expression that may contain NULL.
  • Expr2: It is the targeted value for converting NULL.

NVL function can be used to convert any datatype, but the return value should always be same as the datatype of Expr1.
The datatype of the source and result must match.

Example:
 NVL(Comm,0) / Sal+NVL(Comm,0)
 NVL(HireDate, ’01-JUN-99′)
 NVL(Job, ‘Not Assigned’)

SQL> SELECT EName, Sal, Comm, Sal+NVL(Comm,0)  “Total Sal”
  2  FROM emp;

ENAME  SAL    COMM  Total Sal
———- ———- ———- ———-
SMITH  800 800
ALLEN 1600     300       1900
WARD 1250     500       1750
JONES 2975       2975
MARTIN 1250    1400       2650
BLAKE 2850       2850
CLARK 2450       2450
SCOTT 3000       3000
KING 5000       5000
TURNER 1500       0       1500
ADAMS 1100       1100
JAMES  950 950
FORD 3000       3000
MILLER 1300       1300

14 rows selected.

Leave a Comment

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

Twitter
YouTube
Pinterest
LinkedIn