PolarDB:DECODE expression

Overview

The DECODE expression is a SQL expression that is highly compatible with Oracle. It performs conditional logic in a query based on equality comparisons by comparing an input expression against a series of search-result pairs. If a match is found, it returns the corresponding result. If no values match, it returns an optional default value.

DECODE(expr, search1, result1 [, search2, result2] ... [, default])

How it works
The DECODE expression works like a simplified IF-THEN-ELSE chain or a CASE expression:

1. It first evaluates the expr value.

2. Then, it compares the expr value with search1, search2, and so on.

3. If expr equals a search value, such as search1, DECODE returns the corresponding result value, such as result1, and stops the comparison.

4. If expr does not match any search value, DECODE returns the final default value.

5. If you omit the default value and no match is found, DECODE returns NULL.

Scope

Your PolarDB for PostgreSQL (Compatible with Oracle) cluster must run minor engine version 2.0.14.17.36.0 or later.

Features

• Concise syntax: The DECODE expression has a more compact syntax than a CASE expression. This significantly reduces the amount of code, especially for multiple simple equality checks.

• High readability: For common scenarios such as value mapping, the search-result pair format of the DECODE expression is intuitive and easy to understand.

Examples

The following examples show how to use the DECODE expression in different use cases.

Prepare data

Before you run the examples, connect to your PolarDB cluster and create the following test tables.

-- Create an inventory table
CREATE TABLE inventories (
    product_id NUMBER,
    warehouse_id NUMBER,
    quantity NUMBER
);

-- Insert inventory data
INSERT INTO inventories VALUES (1774, 1, 100);
INSERT INTO inventories VALUES (1775, 2, 200);
INSERT INTO inventories VALUES (1776, 3, 150);
INSERT INTO inventories VALUES (1777, 4, 300);
INSERT INTO inventories VALUES (1778, 5, 250);

-- Create an employees table
CREATE TABLE employees (
    employee_id NUMBER,
    department_id NUMBER,
    salary NUMBER,
    job_id VARCHAR2(10)
);

-- Insert employee data
INSERT INTO employees VALUES (100, 10, 5000, 'IT_PROG');
INSERT INTO employees VALUES (101, 20, 6000, 'SA_REP');
INSERT INTO employees VALUES (102, 30, 4500, 'ST_CLERK');
INSERT INTO employees VALUES (103, 10, 7000, 'IT_PROG');

SELECT
    product_id,
    warehouse_id,
    DECODE(warehouse_id,
           1, 'Southlake',
           2, 'San Francisco',
           3, 'New Jersey',
           4, 'Seattle',
           'Non domestic') AS "Location"
FROM inventories
WHERE product_id < 1779
ORDER BY product_id;

 product_id | warehouse_id |   Location    
------------+--------------+---------------
       1774 |            1 | Southlake
       1775 |            2 | San Francisco
       1776 |            3 | New Jersey
       1777 |            4 | Seattle
       1778 |            5 | Non domestic
(5 rows)

SELECT
    employee_id,
    salary,
    DECODE(department_id,
           10, salary * 0.15,  -- Bonus coefficient for the IT department is 15%
           20, salary * 0.12,  -- Bonus coefficient for the Sales department is 12%
           30, salary * 0.10,  -- Bonus coefficient for the Operations department is 10%
           salary * 0.05) AS bonus  -- Bonus coefficient for other departments is 5%
FROM employees;

 employee_id | salary | bonus 
-------------+--------+-------
         100 |   5000 |   750
         101 |   6000 |   720
         102 |   4500 |   450
         103 |   7000 |  1050
(4 rows)

SELECT
    employee_id,
    department_id,
    DECODE(department_id,
           10, 'IT Department',
           20, 'Sales Department'
           -- No match is provided for department_id=30, and there is no default value
          ) AS dept_name
FROM employees;

 employee_id | department_id |    dept_name     
-------------+---------------+------------------
         100 |            10 | IT Department
         101 |            20 | Sales Department
         102 |            30 | 
         103 |            10 | IT Department
(4 rows)

Compatibility notes

In Oracle, the final data type and length of the return value for a DECODE expression depend on all result and default parameters. To better support this feature, PolarDB for PostgreSQL (Compatible with Oracle) provides the polar_enable_decode_type_mode parameter.

When the polar_enable_decode_type_mode parameter is enabled, the return value's type length for the DECODE expression follows these rules to emulate the behavior of Oracle:

1. All parameters of the expression must be a string literal, NULL, a table column, or a subquery.

2. The type length of all result parameters cannot be unlimited. For example, the TEXT type is not allowed.

3. If these conditions are met, the return type length of the DECODE expression is the maximum type length among all result parameters.

Example: Verify the return value type length

This example creates a view where the return value type length of the DECODE expression is the maximum length of all possible results.

-- Enable compatibility mode
SET polar_enable_decode_type_mode = ON;

-- The job_id column is of type VARCHAR2(10), with a length of 10
-- The length of 'AA' is 2, and the length of 'BBB' is 3
-- Therefore, the final type length of the DECODE expression should be the maximum of the three, which is 10
CREATE OR REPLACE VIEW decode_char_type_test AS
SELECT
    DECODE('A',
           'A', 'AA',    -- Length is 2
           'B', 'BBB',   -- Length is 3
           job_id       -- Length is 10
    ) AS a
FROM employees;

-- Query the metadata of the columns in the view
SELECT
    column_name,
    data_type,
    character_maximum_length
FROM information_schema.columns
WHERE table_name = 'decode_char_type_test'
ORDER BY ordinal_position;

Expected output:
The result shows that character_maximum_length is 10.

 column_name |     data_type     | character_maximum_length 
-------------+-------------------+--------------------------
 a           | character varying |                       10
(1 row)