NVL2 Function

NVL2 Function

NVL2: check for the existence of NOT NULL

Most of us are familiar with the NVL function which checks for the existence of NULL values. Oracle provides a new function called NVL2 which checks for the existence of NOT NULL. The syntax for this function is as follows.

NVL2(expr1,expr2,expr3);

If expr1 is not null then the function will return expr2. Otherwise, the function will return expr3. The expr1 can have any datatype and arguments expr2 and expr3 can be of any datatype other than LONG. The datatype of the return value is that of expr2.

Example:
SQL> select empno,ename,sal,comm,nvl2(comm,1,0) commpre
     from emp order by empno;

EMPNO ENAME SAL COMM COMMPRE
7369 SMITH 800 0
7499 ALLEN 1600 300 1
7521 WARD 1250 500 1
7566 JONES 2975 0
7654 MARTIN 1250 1400 1
7698 BLAKE 2850 0
7782 CLARK 2450 0
7788 SCOTT 3000 0
7839 KING 5000 0
7844 TURNER 1500 0 1
7876 ADAMS 1100 0
7900 JAMES 950 0
7902 FORD 3000 0
7934 MILLER 1300 0

The above query checks for the presence of NOT NULL in the COMM field, and returns 1 where COMM is NOT NULL and 0 where it is NULL.

Here are the differences between the NVL and NVL2 functions:

  • NVL takes 2 arguments while NVL2 takes 3.
  • NVL returns the first argument if the first argument is not null, whereas NVL2 returns the second argument if the first argument is not null and returns the third argument if the first argument is null.
  • In NVL, the datatype of the return is that of the first argument, in NVL2 the datatype of the return is that of the second argument.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: