Decode as If statement : DECODE « Conversion Functions « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. Conversion Functions
  3. DECODE
SQL>
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 job, ename
  2  ,      decode(greatest(msal,2500),2500,'cheap','expensive') as class
  3  from   employees
  4  where  bdate <date '1964-01-01'
  5  order  by decode(job,'DIRECTOR',1,
  6                       'MANAGER',2,
  7                       3);

no rows selected

SQL>
SQL> drop table employees;

Table dropped.








15.2.DECODE
15.2.1.Using the DECODE() Function
15.2.2.SELECT DECODE(1, 2, 1, 3)
15.2.3.Use Decode in table column
15.2.4.Pass multiple search and result parameters to DECODE()
15.2.5.The null may be made more explicit with a DECODE statement
15.2.6.Decode as If statement
15.2.7.Demo range comparison with DECODE
15.2.8.DECODE in the GROUP BY clause
15.2.9.Use decode create dynamic select statement
15.2.10.Use decode as if statement and output 'high' or 'low'
15.2.11.decode column data to output more meaningful information