RelationalDBDesign 





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
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.
GROUPING tells Oracle to summarize. In this case we have extracted region, job title, and salary information and summarized it by region

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