Problem solve Get help with specific problems with your technologies, process and projects.

Use of the NVL2 function

Oracle has a function--NVL2--that checks the existence of NOT NULL. Here's the syntax.

All of us are familiar with the NVL function that checks the existence of the NULL value. Oracle provides a new function NVL2 that checks the existence of NOT NULL. 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 return value is that of expr2. For example:

  SELECT  EMPNO,ENAME,SAL,COMM,NVL2(COMM,1,0) COMMPRE
  FROM EMP
  ORDER BY EMPNO;

--------------------------------------------------------------------------------

   EMPNO 		ENAME            	 SAL       	COMM  	 COMMPRE
-------- 		 ---------- 	---------- --------    -----------
    7499 		ALLEN            	 5000       	   300             1
    7521 		WARD           	 3906.5        500     	     1
    7566 		JONES            	 9297                    	     0
    7654 		MARTIN         	 3906.5       1400            1
    7698 		BLAKE          	 8906.5                            0
    7782 		CLARK          	 7656.5                            0
    7788 		SCOTT            	 9375                                0
    7839 		KING            	 15625                              0
    7844 		TURNER         	 4687.5          0                 1
    7876 		ADAMS          	 3437.5                             0
    7900 		JAMES            	 2969                                0
    7902 		FORD             	 9375                                0
    7934 		MILLER         	 4062.5                            0

----------------------------------------------------------------------------

In the above query I checked for the presence of NOT NULL in the COMM field. Function gave 1 where COMM is not null and 0 where it is NULL. Here is the difference between the NVL and NVL2 functions:

  1. NVL takes 2 arguments while NVL2 takes 3 arguments.
  2. NVL returns the first argument if the first argument is not null; NVL2 returns the second argument if the first argument is not null and returns the third argument if the first argument is null.

For More Information


Dig Deeper on Oracle and SQL

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close