Thursday, 11 October 2012

NVL2 Function In Oracle

The nvl2 (expr1, expr2, expr3) works as follows:
  • if expr1 is not null it returns expr2,
  • If expr1 is null it returns expr3.
SQL> select ename,nvl2(comm,comm,0),comm from emp order by comm;
ENAME      NVL2(COMM,COMM,0)      COMM
---------- ----------------- ---------
TURNER                     0         0
ALLEN                    300       300
WARD                     500       500
MARTIN                  1400      1400
SMITH                      0
JONES                      0
JAMES                      0
MILLER                     0
FORD                       0
ADAMS                      0
BLAKE                      0
CLARK                      0
SCOTT                      0
KING                       0
14 rows selected.

SQL> select ename, vsize (ename) from emp;
ENAME      VSIZE(ENAME)
---------- ------------
SMITH                 5
ALLEN                 5
WARD                  4
JONES                 5
MARTIN                6
BLAKE                 5
CLARK                 5
SCOTT                 5
KING                  4
TURNER                6
ADAMS                 5
JAMES                 5
FORD                  4
MILLER                6
14 rows selected.

SQL> select ename,nvl2(comm,comm,null),comm from emp order by comm;
ENAME      NVL2(COMM,COMM,NULL)       COMM
---------- -------------------- ----------
TURNER                        0          0
ALLEN                       300        300
WARD                        500        500
MARTIN                     1400       1400
SMITH
JONES
JAMES
MILLER
FORD
ADAMS
BLAKE

ENAME      NVL2(COMM,COMM,NULL)       COMM
---------- -------------------- ----------
CLARK
SCOTT
KING
14 rows selected.


SQL> select ename,nvl2(comm,null,0),comm from emp order by comm;
ENAME      NVL2(COMM,NULL,0)       COMM
---------- ----------------- ----------
TURNER                                0
ALLEN                               300
WARD                                500
MARTIN                             1400
SMITH                      0
JONES                      0
JAMES                      0
MILLER                     0
FORD                       0
ADAMS                      0
BLAKE                      0

ENAME      NVL2(COMM,NULL,0)       COMM
---------- ----------------- ----------
CLARK                      0
SCOTT                      0
KING                       0
14 rows selected.

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...

ShareThis