nvl2(comm,12*msal+comm,12*msal) as yearsal : NVL2 « Conversion Functions « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. Conversion Functions
  3. NVL2
SQL>
SQL> create table employees(
  2    empno      NUMBER(4)
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , msal       NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2) ) ;

Table created.

SQL>
SQL>
SQL> insert into employees values(1,'Jason',  'N',  'TRAINER', 2,   date '1965-12-18',  800 , NULL,  10);

1 row created.

SQL> insert into employees values(2,'Jerry',  'J',  'SALESREP',3,   date '1966-11-19',  1600, 300,   10);

1 row created.

SQL> insert into employees values(3,'Jord',   'T' , 'SALESREP',4,   date '1967-10-21',  1700, 500,   20);

1 row created.

SQL> insert into employees values(4,'Mary',   'J',  'MANAGER', 5,   date '1968-09-22',  1800, NULL,  20);

1 row created.

SQL> insert into employees values(5,'Joe',    'P',  'SALESREP',6,   date '1969-08-23',  1900, 1400,  30);

1 row created.

SQL> insert into employees values(6,'Black',  'R',  'MANAGER', 7,   date '1970-07-24',  2000, NULL,  30);

1 row created.

SQL> insert into employees values(7,'Red',    'A',  'MANAGER', 8,   date '1971-06-25',  2100, NULL,  40);

1 row created.

SQL> insert into employees values(8,'White',  'S',  'TRAINER', 9,   date '1972-05-26',  2200, NULL,  40);

1 row created.

SQL> insert into employees values(9,'Yellow', 'C',  'DIRECTOR',10,  date '1973-04-27',  2300, NULL,  20);

1 row created.

SQL> insert into employees values(10,'Pink',  'J',  'SALESREP',null,date '1974-03-28',  2400, 0,     30);

1 row created.

SQL>
SQL>
SQL> select ename, msal, comm
  2  ,      nvl2(comm,12*msal+comm,12*msal) as yearsal
  3  from   employees;

ENAME          MSAL       COMM    YEARSAL
-------- ---------- ---------- ----------
Jason           800                  9600
Jerry          1600        300      19500
Jord           1700        500      20900
Mary           1800                 21600
Joe            1900       1400      24200
Black          2000                 24000
Red            2100                 25200
White          2200                 26400
Yellow         2300                 27600
Pink           2400          0      28800

10 rows selected.

SQL>
SQL> drop table employees;

Table dropped.








15.4.NVL2
15.4.1.NVL2(x, value1, value2) returns value1 if x is not null.
15.4.2.nvl2(comm,12*msal+comm,12*msal) as yearsal
15.4.3.Use nvl2 to deal with null salary
15.4.4.Use the NVL() against a non-initialized BOOLEAN variable: