Use decode as if statement and output 'high' or 'low' : DECODE « Conversion Functions « Oracle PL/SQL Tutorial

SQL> create table emp
  2  ( empno      NUMBER(4)    constraint E_PK primary key
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , sal        NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2)    default 10
 11  ) ;

Table created.

SQL> insert into emp values(1,'Tom','N',   'Coder', 13,date '1965-12-17',  800 , NULL,  20);

1 row created.

SQL> insert into emp values(2,'Jack','JAM', 'Tester',6,date '1961-02-20',  1600, 300,   30);

1 row created.

SQL> insert into emp values(3,'Wil','TF' ,  'Tester',6,date '1962-02-22',  1250, 500,   30);

1 row created.

SQL> insert into emp values(4,'Jane','JM',  'Designer', 9,date '1967-04-02',  2975, NULL,  20);

1 row created.

SQL> insert into emp values(5,'Mary','P',  'Tester',6,date '1956-09-28',  1250, 1400,  30);

1 row created.

SQL> insert into emp values(6,'Black','R',   'Designer', 9,date '1963-11-01',  2850, NULL,  30);

1 row created.

SQL> insert into emp values(7,'Chris','AB',  'Designer', 9,date '1965-06-09',  2450, NULL,  10);

1 row created.

SQL> insert into emp values(8,'Smart','SCJ', 'Coder', 4,date '1959-11-26',  3000, NULL,  20);

1 row created.

SQL> insert into emp values(9,'Peter','CC',   'Designer',NULL,date '1952-11-17',  5000, NULL,  10);

1 row created.

SQL> insert into emp values(10,'Take','JJ', 'Tester',6,date '1968-09-28',  1500, 0,     30);

1 row created.

SQL> insert into emp values(11,'Ana','AA',  'Coder', 8,date '1966-12-30',  1100, NULL,  20);

1 row created.

SQL> insert into emp values(12,'Jane','R',   'Manager',   6,date '1969-12-03',  800 , NULL,  30);

1 row created.

SQL> insert into emp values(13,'Fake','MG',   'Coder', 4,date '1959-02-13',  3000, NULL,  20);

1 row created.

SQL> insert into emp values(14,'Mike','TJA','Manager',   7,date '1962-01-23',  1300, NULL,  10);

1 row created.

SQL>
SQL>
SQL> select job, ename
  2  ,      decode(greatest(sal,2500)
  3               ,2500,'cheap','expensive') as class
  4  from   emp
  5  where  bdate <date '1964-01-01'
  6  order  by decode(job,'Designer',1,'Designer',2,3);

JOB      ENAME    CLASS
-------- -------- ---------
Designer Black    expensive
Designer Peter    expensive
Tester   Mary     cheap
Manager  Mike     cheap
Coder    Smart    expensive
Coder    Fake     expensive
Tester   Wil      cheap
Tester   Jack     cheap

8 rows selected.

SQL>
SQL>
SQL> drop table emp;

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