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.
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