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