jamesravid

 Profile Answers by jamesravid 

• Jul 18th, 2007

 

NVL is a built in function provided by oracle which accepts two parameters. NVL function returns the first parameter if the first parameter is not null  and it returns the second parameter if the first parameter is null.

select emp_no, nvl(comm_prt,0) from employees;

In the above query employee number and his commission percentages are returned. IF commission percentage is null then 0 is returned.

NULL and NOT NULL are used to compare a column or a varaible is null or not.

select emp_name from employees where comm_prt is NULL;

the above query returns employees whose commission percentage is NULL

Prabhat Sahu

 Profile Answers by Prabhat Sahu 

• Nov 28th, 2018

 

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.