Oracle DECODE Function: Syntax and Examples in Oracle 26ai

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 AI Database 26ai. 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 first search and first result matter: Oracle generally converts comparison operands to the first search datatype and the return value to the first result datatype. A first result that is CHAR or null gives a VARCHAR2 return. Numeric-first pairs have additional numeric-precedence rules. See the Oracle 26ai DECODE reference. Reordering pairs can therefore change more than readability.

Search expressions are evaluated in order and later searches are skipped after a match. This is not a promise that incompatible return datatypes will be accepted. Keep comparisons and returned values consistently typed.

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

Do not replace a null-search DECODE mechanically with CASE expression WHEN NULL: use a searched CASE WHEN expression IS NULL. A simple CASE equality comparison does not reproduce DECODE's null matching.

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;

Self-contained DECODE and ROLLUP example

WITH sales (region_name, job_title, amount) AS (
    SELECT 'EAST', 'Editor', 100 FROM dual UNION ALL
    SELECT 'EAST', 'Editor', 200 FROM dual UNION ALL
    SELECT 'WEST', 'Manager', 300 FROM dual
)
SELECT DECODE(GROUPING(region_name), 1, 'All Regions',
              region_name) AS region_label,
       DECODE(GROUPING(job_title), 1, 'All Jobs',
              job_title) AS job_label,
       COUNT(*) AS row_count,
       SUM(amount) AS total_amount
FROM sales
GROUP BY ROLLUP(region_name, job_title)
ORDER BY GROUPING(region_name), region_name,
         GROUPING(job_title), job_title;

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 report_events
WHERE TO_CHAR(event_time, 'MM') = '03';

CREATE INDEX report_events_month_fbi
    ON report_events (
        TO_CHAR(event_time, 'MM')
    );

SELECT COUNT(*)
FROM report_events
WHERE event_time >= DATE '2026-03-01'
  AND event_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.

For the summary-label rules, see Oracle's GROUPING reference; for conditional alternatives, see CASE expressions. Changing DECODE to CASE is not by itself evidence of faster SQL. Compare actual work for representative inputs and preserve the result.

Key points

• DECODE is supported in Oracle AI Database 26ai.
• 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