Oracle Database 23ai introduces several enhancements to the CASE expression, making it more powerful and flexible. In this blog, we’ll explore these enhancements using a sample table and various SELECT statements.

Sample Table: Employees

Let’s start by creating a sample table called Employees:

CREATE TABLE Employees (
    EmployeeID NUMBER PRIMARY KEY,
    FirstName VARCHAR2(50),
    LastName VARCHAR2(50),
    Department VARCHAR2(50),
    Salary NUMBER
);

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary) VALUES (1, 'John', 'Doe', 'HR', 50000);
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary) VALUES (2, 'Jane', 'Smith', 'Finance', 60000);
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary) VALUES (3, 'Emily', 'Jones', 'IT', 70000);
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary) VALUES (4, 'Michael', 'Brown', 'IT', 80000);
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary) VALUES (5, 'Sarah', 'Davis', 'HR', 55000);

Example 1: Basic CASE Expression

The basic CASE expression allows you to perform conditional logic in your SQL queries. Here’s an example that categorizes employees based on their salary:

COL FIRSTNAME for a10
COL LASTNAME for a10
SELECT 
    EmployeeID, 
    FirstName, 
    LastName, 
    Salary, 
    CASE  
        WHEN Salary < 60000 THEN 'Low' 
        WHEN Salary BETWEEN 60000 AND 70000 THEN 'Medium' 
        ELSE 'High' 
    END AS SalaryCategory 
FROM Employees;


EMPLOYEEID FIRSTNAME  LASTNAME       SALARY SALARY
---------- ---------- ---------- ---------- ------
         1 John       Doe             50000 Low
         2 Jane       Smith           60000 Medium
         3 Emily      Jones           70000 Medium
         4 Michael    Brown           80000 High
         5 Sarah      Davis           55000 Low

Example 2: Using Dangling Predicates

Oracle 23ai allows for dangling predicates, enabling comparisons other than equality. Here’s an example:

COL FIRSTNAME for a10
COL LASTNAME for a10
SELECT 
    EmployeeID, 
    FirstName, 
    LastName, 
    Salary, 
    CASE  SALARY
        WHEN  < 60000 THEN 'Low' 
        WHEN  > 60000, < 70000 THEN 'Medium' 
        WHEN  >= 70000 THEN 'High' 
    END AS SalaryCategory 
FROM Employees;

EMPLOYEEID FIRSTNAME  LASTNAME       SALARY SALARY
---------- ---------- ---------- ---------- ------
         1 John       Doe             50000 Low
         2 Jane       Smith           60000 Medium
         3 Emily      Jones           70000 Medium
         4 Michael    Brown           80000 High
         5 Sarah      Davis           55000 Low