RelationalDBDesign 





Oracle SQL Tuning  «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 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