ANSI SQL Extensions  «Prev  Next»

Lesson 5 DECODE built-in function with Oracle SQL
Objective Use built-in function DECODE within the context of Oracle PL/SQL

DECODE built-in function - Oracle PL/SQL

As we demonstrated in a previous lesson, DECODE is very good at transforming a list of returned values from one string to another.
DECODE is also very useful when dealing with a small number of possible values, but it can make SQL statements long and cumbersome if there are hundreds of values. Below we see a SQL query to change region_codes to meaningful names:
Select
  Emp_name,
  DECODE (region,10, ’EAST’,
                 20, ’WEST’,
                 30, ’SOUTH’,
                 40, ’NORTH’,
                      ’NONE’)
from employee;
Barrett   WEST
Burleeze  EAST
Dahlstrom NONE
3 rows selected.

DECODE with GROUPING and ROLLUP

The DECODE function within Oracle becomes even more powerful when it is combined with other built-in functions.
The MouseOver below shows what can happen when you use the GROUPING and ROLLUP BIFs with DECODE.

decode Grouping
  1. GROUPING tells Oracle to summarize. In this case we have extracted region, job title, and salary information and summarized it by region
  2. We have rolled up the totals by region_name and by job_title.
  3. AVG provides a nationwide average for the number of employees and the average salary.
  4. If this were written without BIFs, it would have become a cumbersome and challenging programming task.

Grouping rollup bifs
Now, let us look at some of the other Oracle-supplied extensions to Oracle SQL.

Oracle Decode - Exercise

First, click the Exercise link below to practice writing an SQL statement that uses a built-in function.
Oracle Decode - Exercise