ajeet kumar

• May 8th, 2006

 

We are using NVL2 for checking the multiple conding. NVL2(Exp1,Exp2,Exp3). If Exp1 is not null then funcion will return Exp2 else function will return Exp3.

Kathirvel R

• Jul 21st, 2006

 

NVL :: We can use when we get the null values..THat is we can replace the null values using the NVL function.

EX : select nvl(sal,0) from emp

NVL2 :: we can check the field is null or not null then we give a value

EX :Select nvl2(sal,sal+100,0) from emp

       ie,if sal is not null we add sal+100.if null we give 0

Amit Vashishtha

• Dec 19th, 2006

 

NVL2 is a function which is used to deal with NULL values. NVL2 function examines the first expression. If the first expression is not NULL, then NVL2 function returns the second expression. If the first expression is NULL, then the third expression is returned. Syntax is NVL2(Commision,sal*commision,sal). Example below will clear all things-

Suppose we have a table which has fields-

Empno,Ename,Deptno,sal,Manager,job,hiredate and Commision. Commision field has NULL values. Now we want to replace null values with string 'NO Comm' and show Commision where it is, then

Select Ename,job,sal,NVL2(Commision,sal*commision,sal) "Revised Salary" from emp;

run it and check it.

mala321

 Profile Answers by mala321 

• Dec 24th, 2007

 

Hello,


NVL2  function is used to substitute  a value for an expression

NVL2(string, value_if _not_null_value, value_null_value)
 

if the string  contains  a  null vlaue  it is replaced with the  2 (Value_null_vale)
else it is substituted with the not_null_value

eg:  a=34;
NVL2(a, 999, NULL)

here a is substiuted with 999.


bye
mala

r.kothandaraman

 Profile Answers by r.kothandaraman 

• Jun 13th, 2009

 

We can set all the null value columns to some other values after that we can get all the corresponding rows and columns of the table.