Enhanced Control Flow with CASE in Oracle Database 23c

Oracle Database 23c introduces significant enhancements to the simple CASE statement and expression, offering increased flexibility and alignment with the SQL:2003 standard. These enhancements include: Dangling Predicates and Multiple Choices or Conditions in a Single WHEN Clause. We will demonstrate those enhancements in this article.

Demonstration setup

The code examples in this article is based on the following table and data:

CREATE TABLE employees (
   employee_id NUMBER PRIMARY KEY,
   job_id VARCHAR2(10),
   salary NUMBER
);

INSERT INTO employees VALUES
  (100, 'IT_PROG', 6000),
  (101, 'SA_REP', 8000),
  (102, 'ST_CLERK', 4000),
  (103, 'AD_VP', 12000),
  (104, 'AC_ACCOUNT', 5500);
commit;

Dangling Predicates:

What they are: Dangling predicates are expressions without a left operand.
Pre-23c approach: Required full comparisons within each WHEN clause.
23c approach: Allows dangling predicates, simplifying conditional logic.

The following examples demonstrates the concept:

-- pre-23c
CASE 
  WHEN job_id = 'IT_PROG' THEN 'Programmer'
  WHEN job_id = 'SA_REP' THEN 'Salesperson'
  WHEN job_id IN ('ST_CLERK', 'AD_VP') THEN 'Clerical'  -- this is not equality, so we need to use searched CASE
  ELSE 'Other'
END

-- in 23c
CASE job_id
  WHEN 'IT_PROG' THEN 'Programmer'
  WHEN 'SA_REP' THEN 'Salesperson'
  WHEN  IN ('ST_CLERK', 'AD_VP') THEN 'Clerical'  -- Dangling predicate
  ELSE 'Other'
END

Observe in the second example, job_id is used straight after the CASE keyword. It is implicitly considered in the left hand side of all the comparison expressions. In pre-23c, this is possible only in equality predicate.

Unfortunately, the full capabilities these enhancements in the CASE statement are available in the PL/SQL and not in the SQL. Here is what we would get if we try to use it straight in a SELECT statement:

SELECT employee_id,
CASE job_id
  WHEN 'IT_PROG' THEN 'Programmer'
  WHEN 'SA_REP' THEN 'Salesperson'
  WHEN  IN ('ST_CLERK', 'AD_VP') THEN 'Clerical'  -- Dangling predicate
  ELSE 'Other'
END job_category
FROM EMPLOYEES ;

Error at Command Line : 5 Column : 9
Error report -
SQL Error: ORA-00936: missing expression
00936. 00000 -  "missing expression"
*Cause:
*Action:

The workaround to address this issue is to embed the CASE expression into a WITH function as follows:

WITH
  FUNCTION get_job_category(p_job_id VARCHAR2)
  RETURN VARCHAR2 AS
BEGIN
 return
  CASE p_job_id
    WHEN 'IT_PROG' THEN 'Programmer'
    WHEN 'SA_REP' THEN 'Salesperson'
    WHEN IN ('ST_CLERK', 'AD_VP') THEN 'Clerical'
    ELSE 'Other'
  END;
END;
SELECT employee_id, get_job_category(job_id) job_category
FROM employees;

   EMPLOYEE_ID JOB_CATEGORY
______________ _______________
           100 Programmer
           101 Salesperson
           102 Clerical
           103 Clerical
           104 Other

The workaround to address this issue is to embed the CASE expression into a WITH function as follows:

-- Pre-23c 
SELECT employee_id,
  CASE 
    WHEN salary > 10000 THEN 'Top Tier'
    WHEN salary <= 4000 THEN 'Low Tier'
    ELSE 'Mid Tier'
  END AS salary_tier
FROM employees;


-- in 23c
with
  FUNCTION get_salary_tier(p_salary NUMBER)
  RETURN VARCHAR2
IS
  v_salary_tier VARCHAR2(20);
BEGIN
  CASE p_salary
    WHEN > 10000 THEN v_salary_tier := 'Top Tier';
    WHEN BETWEEN 4001 AND 10000 THEN v_salary_tier := 'Mid Tier';  -- Dangling predicate
    ELSE v_salary_tier := 'Low Tier';
  END CASE;
  RETURN v_salary_tier;
END;
SELECT employee_id, get_salary_tier(salary) salary_tier
FROM employees;
/


   EMPLOYEE_ID SALARY_TIER
______________ ______________
           100 Mid Tier
           101 Mid Tier
           102 Low Tier
           103 Top Tier
           104 Mid Tier

Multiple Choices or Conditions in a Single WHEN Clause:

What it is: Combine multiple choices or conditions within a single WHEN clause.
Pre-23c approach: Required separate WHEN clauses for each choice.
23c approach: Concisely handles multiple choices.

Example (multiple choices):
  CASE p_job_id
    WHEN 'IT_PROG'           THEN 'Programmer'
    WHEN 'SA_REP'            THEN 'Salesperson'
    WHEN 'ST_CLERK', 'AD_VP' THEN 'Clerical' -- multiple choices
    ELSE 'Other'
  END CASE;

Example (multiple conditions):
  CASE p_salary
    WHEN > 10000        THEN 'Top Tier'
    WHEN <= 4000        THEN 'Low Tier'
    WHEN >4000, <=10000 THEN 'Mid Tier'  -- multiple conditions
  END CASE;

Enhancement Benefits

Cleaner and more concise code
Improved readability
Reduced redundancy
Better alignment with SQL standards

Conclusion

These enhancements to the simple CASE statement and expression make PL/SQL code more expressive and easier to maintain, offering a welcome improvement for developers working with Oracle Database 23c.