Use DECODE and SIGN functions on the ORD table : DECODE « Conversion Functions « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Conversion Functions
  3. DECODE

Use DECODE and SIGN functions on the ORD table

   
SQL>
SQL>
SQL>
SQL>
SQL> create table ord(
  2           order_no               integer          primary key
  3          ,cust_no                integer
  4          ,order_date             date not null
  5          ,total_order_price      number(7,2)
  6          ,deliver_date           date
  7          ,deliver_time           varchar2(7)
  8          ,payment_method         varchar2(2)
  9          ,emp_no                 number(3,0)
 10          ,deliver_name           varchar2(35)
 11          ,gift_message           varchar2(100)
 12  );

Table created.

SQL>
SQL>
SQL> insert into ord(order_no,cust_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
  2           values(1,1,'14-Feb-2002', 23.00, '14-Feb-2002', '12 noon', 'CA',1, null, 'Gift for wife');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(2,1,'14-Feb-2003', 510.98, '14-feb-2003', '5 pm', 'NY',7, 'Rose Ted', 'Happy Valentines Day to Mother');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(3, 2,'14-Feb-2004', 315.99, '14-feb-2004', '3 pm', 'VS',2, 'Ani Forest', 'Happy Valentines Day to Father');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(4, 2,'14-Feb-1999', 191.95, '14-feb-1999', '2 pm', 'NJ',2, 'O. John', 'Happy Valentines Day');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message    )
  2           values(5, 6,'4-mar-2002', 101.95, '5-mar-2002', '2:30 pm', 'MO'   , 2, 'Cora', 'Happy Birthday from John');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(6, 9,'7-apr-2003', 221.95, '7-apr-2003', '3 pm', 'MA', 2, 'Sake Keith', 'Happy Birthday from Joe' );

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(7, 9,'20-jun-2004', 315.95, '21-jun-2004', '12 noon', 'BC', 2, 'Jessica Li', 'Happy Birthday from Jessica');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values (8, 12, '31-dec-1999', 135.95, '1-jan-2000', '12 noon', 'DI',      3, 'Larry', 'Happy New Year from Lawrence');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values (9, 12, '26-dec-2003', 715.95, '2-jan-2004', '12 noon', 'SK',7, 'Did', 'Happy Birthday from Nancy' );

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(10, 4, sysdate-1, 119.95, sysdate+2, '6:30 pm', 'VG',2, 'P. Jing', 'Happy Valentines Day to Jason');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(11, 2, sysdate, 310.00, sysdate+2, '3:30 pm', 'DC',2, 'C. Late', 'Happy Birthday Day to Jack');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
  2           values(12, 7, sysdate-3, 121.95, sysdate-2, '1:30 pm', 'AC',2, 'W. Last', 'Happy Birthday Day to You');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
  2           values(13, 7, sysdate, 211.95, sysdate-4, '4:30 pm', 'CA',2, 'J. Bond', 'Thanks for hard working');

1 row created.

SQL>
SQL>
SQL> select
  2   decode (sign (total_order_price - 30) ,
  3                     -1 , 'Small order',
  4                     0  , 'Medium Order',
  5                     +1 , 'Large Order' )
  6              AS size_order,
  7   count(*)   AS num_orders
  8  from ord
  9   group by
 10    decode (sign (total_order_price - 30) ,
 11                     -1 , 'Small order',
 12                     0  , 'Medium Order',
 13                     +1 , 'Large Order' )
 14  /
SIZE_ORDER   NUM_ORDERS
------------ ----------
Small order           1
Large Order          12

2 rows selected.

SQL>
SQL> drop table ord;

Table dropped.

SQL>
SQL> --








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.Extract any procedure, function or package
9.Decode the result from row_number over, partition by, order by
10.Decode a column with substr and instr
11.DECODE and substr function
12.Format nullable heading
13.DECODE in the GROUP BY clause
14.Demo range comparison with DECODE
15.Use decode as if statement and output 'high' or 'low'
16.Use decode create dynamic select statement
17.decode column data to output more meaningful information