Using the Oracle DECODE Function

Oracle's DECODE function performs compact, equality-based value translation. It compares one expression with a sequence of search values and returns the result associated with the first match. This makes it useful for converting stored codes into readable labels, handling a small set of known values, and labeling subtotal or grand-total rows generated by GROUPING and ROLLUP.

DECODE remains supported in Oracle Database 23ai. It is an Oracle-specific SQL function rather than an ANSI SQL construct. In PL/SQL, it is normally used inside a SQL statement, such as a SELECT ... INTO, cursor query, view definition, or DML statement. For new SQL, CASE is often preferred because it is more portable and supports a wider range of conditions, but DECODE remains important when maintaining established Oracle applications.

DECODE syntax and evaluation

DECODE(
    expression,
    search_value_1, result_1,
    search_value_2, result_2,
    ...,
    default_result
)

SELECT
    emp_name,
    DECODE(
        region_code,
        10, 'EAST',
        20, 'WEST',
        30, 'SOUTH',
        40, 'NORTH',
        'NONE'
    ) AS region_name
FROM employee;

EMP_NAME   REGION_NAME
---------- -----------
BARRETT    WEST
BURLEEZE   EAST
DAHLSTROM  NONE

Equivalent CASE expression

A simple CASE expression can perform the same translation:

SELECT
    emp_name,
    CASE region_code
        WHEN 10 THEN 'EAST'
        WHEN 20 THEN 'WEST'
        WHEN 30 THEN 'SOUTH'
        WHEN 40 THEN 'NORTH'
        ELSE 'NONE'
    END AS region_name
FROM employee;

The two forms are similar for equality comparisons. CASE, however, also supports searched conditions such as ranges, inequalities, compound predicates, and tests involving multiple columns:

CASE
    WHEN salary IS NULL THEN 'NOT ASSIGNED'
    WHEN salary < 50000 THEN 'BELOW RANGE'
    WHEN salary > 150000 THEN 'ABOVE RANGE'
    ELSE 'WITHIN RANGE'
END

Use DECODE when a compact Oracle-specific equality mapping is easy to read. Use CASE when the logic is more complex, portability matters, or future maintainers are likely to understand the standard syntax more quickly.

Datatype behavior and implicit conversion

The expression and search values must be comparable. Oracle can perform implicit conversions, but mixing character and numeric datatypes can make a query dependent on session settings or cause conversion errors. Consistent datatypes are safer:

DECODE(region_code, 10, 'EAST', 20, 'WEST', 'NONE')

The return values must also be compatible. In the preceding expression, every result is character data. When a numeric or date result must be displayed as text, convert it explicitly with TO_CHAR.

How DECODE handles NULL

DECODE has an Oracle-specific null comparison rule: it treats two null values as equal. This differs from an ordinary SQL comparison, where NULL = NULL evaluates to UNKNOWN.

SELECT
    emp_name,
    DECODE(
        commission_pct,
        NULL, 'NO COMMISSION',
        'COMMISSION ASSIGNED'
    ) AS commission_status
FROM employee;

The equivalent CASE expression is often more explicit:

CASE
    WHEN commission_pct IS NULL THEN 'NO COMMISSION'
    ELSE 'COMMISSION ASSIGNED'
END

This special null behavior is useful when reading legacy code, but it should not be confused with the normal three-valued logic used by SQL predicates.

DECODE with GROUPING and ROLLUP

GROUP BY ROLLUP(region_name, job_title)

GROUPING(region_name)

DECODE(
    GROUPING(region_name),
    1, 'All Regions',
    region_name
)

SELECT
    DECODE(
        GROUPING(r.region_name),
        1, 'All Regions',
        r.region_name
    ) AS region_name,
    DECODE(
        GROUPING(e.job_title),
        1, 'All Jobs',
        e.job_title
    ) AS job_title,
    COUNT(*) AS employee_count,
    ROUND(AVG(e.sal) * 12, 2) AS avg_annual_salary
FROM employee e
JOIN region r
    ON r.region_id = e.region_id
GROUP BY ROLLUP(
    r.region_name,
    e.job_title
)
ORDER BY
    GROUPING(r.region_name),
    r.region_name,
    GROUPING(e.job_title),
    e.job_title;

Legacy image walkthrough

The original image remains useful because it visually connects DECODE, GROUPING, ROLLUP, COUNT, and AVG. Its SQL and displayed output are transcribed below.

What each function contributes

ROLLUP

ROLLUP generates hierarchical grouping levels from right to left. For:

ROLLUP(region_name, job_title)

Oracle creates these grouping sets:

(region_name, job_title)
(region_name)
()

The empty grouping set represents the grand total.

GROUPING

GROUPING(column) identifies whether a null was generated for a subtotal or grand-total row. It does not itself perform the summarization; ROLLUP creates the grouping levels.

DECODE

DECODE translates the numeric result of GROUPING into labels such as All Jobs and All Regions.

COUNT and AVG

COUNT(*) reports the number of rows contributing to each group. If sal stores a monthly salary, AVG(sal) * 12 annualizes the average. A descriptive alias improves the report:

ROUND(AVG(sal) * 12, 2) AS avg_annual_salary

Frequently used Oracle SQL built-in functions

Functions in predicates and index usage

SELECT COUNT(*)
FROM perfstat.stats$snapshot
WHERE TO_CHAR(snap_time, 'MM') = '03';

CREATE INDEX stats_snapshot_month_fbi
    ON perfstat.stats$snapshot (
        TO_CHAR(snap_time, 'MM')
    );

SELECT COUNT(*)
FROM perfstat.stats$snapshot
WHERE snap_time >= DATE '2026-03-01'
  AND snap_time <  DATE '2026-04-01';

TO_CHAR(date_column, 'MM')
TO_CHAR(date_column, 'MON')
TO_CHAR(date_column, 'FMMonth')

Choosing DECODE, CASE, or a lookup table

Using DECODE from PL/SQL

Because DECODE is a SQL function, PL/SQL code normally invokes it inside a SQL statement:

DECLARE
    v_region_name VARCHAR2(20);
BEGIN
    SELECT DECODE(
               region_code,
               10, 'EAST',
               20, 'WEST',
               30, 'SOUTH',
               40, 'NORTH',
               'NONE'
           )
    INTO v_region_name
    FROM employee
    WHERE employee_id = 100;

    DBMS_OUTPUT.PUT_LINE(v_region_name);
END;
/

For procedural branching outside a SQL statement, use PL/SQL IF or CASE statements instead.

Key points

• DECODE is supported in Oracle Database 23ai.
• It compares one expression with search values in order and returns the result associated with the first match.
• The optional last argument is the default result; without it, an unmatched value returns NULL.
• Oracle DECODE treats two nulls as equal for its comparison semantics.
• CASE is usually preferred for new standards-oriented SQL and complex conditions.
• ROLLUP creates detail groups, subtotals, and a grand total.
• GROUPING distinguishes rollup-generated null placeholders from stored null values.
• DECODE(GROUPING(...), 1, label, column) assigns readable labels to summary rows.
• A function applied to an indexed column does not automatically force a full table scan, but it can prevent direct use of a conventional index.
• A matching function-based index or an index-friendly range predicate can provide a better access path.

The next lesson examines additional Oracle-supplied SQL extensions and how they simplify reporting and data transformation.

Oracle DECODE Exercise