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

Oracle DECODE built-in function

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 diagram below shows what can happen when you use the 1) GROUPING and 2) 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.

Frequently used Oracle SQL BIFs

  1. to_char: The to_char function is especially useful for translating DATE datatypes and converting numeric columns to character representations.
  2. upper: The upper function is often used in queries that search text columns and ensure that retrievals of case-sensitive data are properly serviced.
  3. lower: The lower function is used to convert text to a lowercase representation and is quite useful when searching for strings in text.
  4. substr: The substr function is used to extract sub-strings in a large character column. This is commonly used to extract subsets from large character datatype columns such as subsets of telephone numbers.
  5. decode: The decode function is used to translate values in an SQL statement from a cryptic abbreviation to a readable value. For example, the decode function can translate two-digit State names into the full name of the State.

We must remember that these "built in functions" will cause the SQL optimizer to perform a full-table scan unless a function-based index is created to match the BIF. The most common example of an Oracle BIF is the use of the to_char function to translate a column with a DATE datatype. For example, the following query will display the number of STATSPACK snapshots that occurred in the month of March. 
select count(*)
from 
perfstat.stats$snapshot
where
to_char(snap_time,'MON') = 'Mar';

If we were using a generic index on the snap_time column, the to_char function would not be able to utilize the DATE index. However, with the use of built-in functions, an index can be built on 
to_char(snap_time,'Mon')

and the SQL query could avoid a full-table scan.
To get the "month" from a DATE datatype you simply: 
to_char( dt_column, 'mm' ) -- returns 01..12
to_char( dt_column, 'MON' ) -- returns JAN, FEB, ... DEC in your language
to_char( dt_column, 'Month' ) -- returns January, ... December in your language

GROUPING and ROLLUP BIFs
1) Grouping to summarize 2) rolled up the totals 3) determine the average 4) benefits of built-in functions 
SELECT
  DECODE (GROUPING(region_name), 1, 'All Regions' , rname)
AS name'
  DECODE (GROUPING(job_title), 1, 'All Jobs', job_title)
AS job_title,
  COUNT(*) "#", AVG((sal) * 12 "Avg. Sal"
FROM
  Employee e,
  Region r
WHERE 
  r.region_name = e.region_name
GROUP BY ROLLUP (rname, job_title);
1) Grouping to summarize 2) rolled up the totals 3) determine the average 4) benefits of built-in functions
  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.

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

SEMrush Software