decode column data to output more meaningful information : DECODE « Conversion Functions « Oracle PL/SQL Tutorial

SQL> create table gift(
  2           gift_id                integer         primary key
  3          ,emp_id                integer
  4          ,register_date              date not null
  5          ,total_price        number(7,2)
  6          ,deliver_date           date
  7          ,deliver_time           varchar2(7)
  8          ,payment        varchar2(2)
  9          ,emp_no                 number(3,0)
 10          ,deliver_name           varchar2(35)
 11          ,message        varchar2(100)
 12  );

Table created.

SQL>
SQL> insert into gift(gift_id,emp_id,register_date,total_price,deliver_date,deliver_time,payment,emp_no,deliver_name,message)values
  2                 (1,1,'14-Feb-1999', 123.12, '14-Feb-1999', '12 noon', 'CA',1, null, 'Happy Birthday to you');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time ,payment ,emp_no,deliver_name ,message)values
  2                 (2,1,'14-Feb-1999', 50.98, '14-feb-1999', '1 pm', 'CA',7, 'name1', 'Happy Birthday');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
  2                 (3, 2,'14-Feb-1999', 35.99, '14-feb-1999', '1 pm', 'VS',2, 'Tom', 'Happy Birthday');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
  2                 (4, 2,'14-Feb-1999', 19.95, '14-feb-1999', '5 pm', 'CA',2, 'Mary', 'Happy Birthday');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
  2                 (5, 6,'4-mar-1999', 10.95, '5-mar-1999', '4:30 pm', 'VS', 2, 'Jack', 'Happy Birthday');

1 row created.

SQL>
SQL>
SQL>
SQL> select gift_id, decode(payment,
  2  'VS', 'VISA Card Standard',
  3  'VG', 'VISA Gold Card',
  4  'AX', 'American Express',
  5  'MC', 'Master Card',
  6  'DI', 'Discover Card',
  7  'CA', 'Cash',
  8  'CK', 'Check',
  9  'Unknown')
 10    from gift ;

   GIFT_ID DECODE(PAYMENT,'VS
---------- ------------------
         1 Cash
         2 Cash
         3 VISA Card Standard
         4 Cash
         5 VISA Card Standard

5 rows selected.

SQL>
SQL>
SQL> drop table gift;

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