Use decode as if statement and output 'high' or 'low'

    

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.

Related examples in the same category

1.	Syntax: DECODE
2.	Decode char value to number
3.	Decode statement could be easily rewritten using the CASE statement
4.	Decode for more than one key value pair
5.	Use Decode as if statement to logic like 'if then else'
6.	DECODE and GROUPING
7.	Create a report that shows number of customers per state.
8.	Use DECODE and SIGN functions on the ORD table
9.	Extract any procedure, function or package
10.	Decode the result from row_number over, partition by, order by
11.	Decode a column with substr and instr
12.	DECODE and substr function
13.	Format nullable heading
14.	DECODE in the GROUP BY clause
15.	Demo range comparison with DECODE
16.	Use decode create dynamic select statement
17.	decode column data to output more meaningful information

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

Related examples in the same category