ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Use of the NVL2 Function[akadia]

Use of the NVL2 Function[akadia]

原创 Linux操作系统 作者:jlandzpa 时间:2019-02-22 18:15:06 0 删除 编辑

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.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/936/viewspace-60615/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2001-10-12

  • 博文量
    240
  • 访问量
    172285