Decode for more than one key value pair : DECODE « Conversion Functions « Oracle PL / SQL






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

Decode for more than one key value pair

   
SQL>
SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );

Table created.

SQL>
SQL> INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');

1 row created.

SQL> INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');

1 row created.

SQL> INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');

1 row created.

SQL> INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

1 row created.

SQL>
SQL> select * from dept;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.

SQL>
SQL> select dname, decode( loc, 'BOSTON', 'Red Sox fans',
  2                             'CHICAGO', 'White Sox fans',
  3                             'DALLAS', 'Astros fans',
  4                             'NEW YORK', 'Mets fans' ) "Baseball Team"
  5  from dept;
DNAME          Baseball Team
-------------- --------------
ACCOUNTING     Mets fans
RESEARCH       Astros fans
SALES          White Sox fans
OPERATIONS     Red Sox fans

4 rows selected.

SQL>
SQL> drop table dept;

Table dropped.

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.Use Decode as if statement to logic like 'if then else'
5.DECODE and GROUPING
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