DECODE and GROUPING : DECODE « Conversion Functions « Oracle PL / SQL






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

DECODE and GROUPING

   
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 * from ord;
  ORDER_NO    CUST_NO ORDER_DATE           TOTAL_ORDER_PRICE DELIVER_DATE         DELIVER PA     EMP_NO DELIVER_NAME
---------- ---------- -------------------- ----------------- -------------------- ------- -- ---------- -----------------------------------
GIFT_MESSAGE
----------------------------------------------------------------------------------------------------
         1          1 14-FEB-2002 00:00:00                23 14-FEB-2002 00:00:00 12 noon CA          1
Gift for wife

         2          1 14-FEB-2003 00:00:00            510.98 14-FEB-2003 00:00:00 5 pm    NY          7 Rose Ted
Happy Valentines Day to Mother

         3          2 14-FEB-2004 00:00:00            315.99 14-FEB-2004 00:00:00 3 pm    VS          2 Ani Forest
Happy Valentines Day to Father

         4          2 14-FEB-1999 00:00:00            191.95 14-FEB-1999 00:00:00 2 pm    NJ          2 O. John
Happy Valentines Day

         5          6 04-MAR-2002 00:00:00            101.95 05-MAR-2002 00:00:00 2:30 pm MO          2 Cora
Happy Birthday from John

         6          9 07-APR-2003 00:00:00            221.95 07-APR-2003 00:00:00 3 pm    MA          2 Sake Keith
Happy Birthday from Joe

         7          9 20-JUN-2004 00:00:00            315.95 21-JUN-2004 00:00:00 12 noon BC          2 Jessica Li
Happy Birthday from Jessica

         8         12 31-DEC-1999 00:00:00            135.95 01-JAN-2000 00:00:00 12 noon DI          3 Larry
Happy New Year from Lawrence

         9         12 26-DEC-2003 00:00:00            715.95 02-JAN-2004 00:00:00 12 noon SK          7 Did
Happy Birthday from Nancy

        10          4 15-JUN-2008 17:42:32            119.95 18-JUN-2008 17:42:32 6:30 pm VG          2 P. Jing
Happy Valentines Day to Jason

        11          2 16-JUN-2008 17:42:33               310 18-JUN-2008 17:42:33 3:30 pm DC          2 C. Late
Happy Birthday Day to Jack

        12          7 13-JUN-2008 17:42:34            121.95 14-JUN-2008 17:42:34 1:30 pm AC          2 W. Last
Happy Birthday Day to You

        13          7 16-JUN-2008 17:42:35            211.95 12-JUN-2008 17:42:35 4:30 pm CA          2 J. Bond
Thanks for hard working


13 rows selected.

SQL>
SQL> SELECT DECODE(GROUPING(cust_no), 1,'All Custs', cust_no) AS cust_no,
  2         DECODE(GROUPING(payment_method), 1, 'All PMs', payment_method) AS pay_meth,
  3         to_char(sum(total_order_price), '999.99') AS sales
  4  FROM ord
  5  WHERE cust_no IN (2,12)
  6  GROUP BY CUBE (cust_no, payment_method)
  7  ORDER BY 1,3
  8  /
CUST_NO                                  PAY_MET SALES
---------------------------------------- ------- -------
12                                       DI       135.95
12                                       SK       715.95
12                                       All PMs  851.90
2                                        NJ       191.95
2                                        DC       310.00
2                                        VS       315.99
2                                        All PMs  817.94
All Custs                                DI       135.95
All Custs                                NJ       191.95
All Custs                                DC       310.00
All Custs                                VS       315.99
All Custs                                SK       715.95
All Custs                                All PMs #######

13 rows selected.

SQL>
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.Create a report that shows number of customers per state.
7.Use DECODE and SIGN functions on the ORD table
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