This tutorial will explain how to use Oracle Case Statement expression with basic syntax and many examples for better understanding. And don’t forget to take the quiz

What is Oracle Case Statement? Oracle Case Statement is similar to the IF-THEN-ELSE statement in PL/SQL but with the advantage of using it inside SQL without calling a procedure. We can use case statements inside PL/SQL program. It comes in two types (Simple Case Statement and Searched Case Statement ), and the case statement has a limit of 255 expressions maximum.

Syntax:

CASE ([Simple case expression] | [Searched case expression]) (ELSE clause) END

Simple Case Syntax:

CASE [Expression | column] WHEN (Comparison expression) THEN {return expression} ELSE [else expression | column] END

Searched Case Syntax:

CASE WHEN [condition/s] THEN {return expression} ELSE [else expression | column] END

Notes:

• In a simple oracle case statement, Oracle search starts with the first WHEN .. THEN pair whether the comparison expression is equal to the expression or column or not and if so, case statement will return {return expression}.
• If none of the WHEN .. THEN pair meet this condition, and an ELSE clause exists, then Oracle returns {else expression}.
• If none of the WHEN .. THEN pair meet this condition, and no ELSE clause is specified, then Oracle returns NULL.
• We can’t specify NULL as literal.
• In the searched case statement, Oracle starts from lift to right and takes every condition until one condition is evaluated with true, then Oracle STOP and returns {return expression}.
• If all conditions are evaluated with FALSE, and an ELSE clause exists, then Oracle returns {else expression}.
• If all conditions are evaluated with FALSE, and no ELSE clause is specified, then Oracle returns NULL.
• All results of the CASE statement for both simple and searched must be the same data type.
• If one of the results in any WHEN .. THEN pair has a different datatype other than others, Oracle will raise an ORA-00932.
• We have limited to 255 expressions.

Sample Setup:

DROP TABLE emps PURGE;

CREATE TABLE emps (
    employee_no     NUMBER  CONSTRAINT pk_emps PRIMARY KEY,
    name            VARCHAR2(50),
    salary          NUMBER(10,2),
    manager_no      NUMBER,
    department_no   NUMBER
);

Data Insertion:

INSERT INTO "HR"."EMPS" (EMPLOYEE_NO, NAME, SALARY, MANAGER_NO, DEPARTMENT_NO) VALUES ('145', 'John', '14000', '100', '80');

INSERT INTO "HR"."EMPS" (EMPLOYEE_NO, NAME, SALARY, MANAGER_NO, DEPARTMENT_NO) VALUES ('146', 'Seleem', '13500', '100', '80');

INSERT INTO "HR"."EMPS" (EMPLOYEE_NO, NAME, SALARY, MANAGER_NO, DEPARTMENT_NO) VALUES ('170', 'Mark', '10500', '161', '70');

INSERT INTO "HR"."EMPS" (EMPLOYEE_NO, NAME, SALARY, MANAGER_NO, DEPARTMENT_NO) VALUES ('175', 'Louise', '12900', '100', '50');

INSERT INTO "HR"."EMPS" (EMPLOYEE_NO, NAME, SALARY, MANAGER_NO, DEPARTMENT_NO) VALUES ('180', 'Magy', '7000', '180', '30');

INSERT INTO "HR"."EMPS" (EMPLOYEE_NO, NAME, SALARY, MANAGER_NO, DEPARTMENT_NO) VALUES ('207', 'Rose', '15450', '170', '70');

COMMIT;

Sample Data:

Let’s take some examples of Oracle case statements and how to use them:

Simple Case Example :

SELECT employee_no
      ,name
      , (CASE department_no
           WHEN 80 THEN 'Information Technology'
           WHEN 70 THEN 'Human Resource'
           WHEN 50 THEN 'Accounting'
           ELSE 'Other Department'
         END)
         employee_departments
FROM   emps;

Output:

In the above example, we have a select statement that uses a simple case statement that is column-based, but we use the oracle case when multiple conditions are available.

Searched Case Example :

SELECT employee_no
      ,name
      , (CASE
           WHEN salary BETWEEN 1000 AND 4000 THEN 'Very Low'
           WHEN salary BETWEEN 4001 AND 8000 THEN 'Low'
           WHEN salary BETWEEN 8001 AND 12000 THEN 'Medium'
           WHEN salary BETWEEN 12001 AND 15000 THEN 'High'
           ELSE 'No rang'
         END)
         salary_rang
FROM   emps;

Output

In the above example, we used searched case statement or searched case expression that is column-based.

Oracle Case Statement in WHERE Clause Example

We can use a case statement inside the WHERE clause in case we need to apply a conditional where clause.

SELECT employee_no, name, department_no
FROM   emps
WHERE  (CASE
          WHEN department_no = 50 THEN 1
          WHEN department_no = 70 THEN 1
          ELSE 0
        END) = 1;

In the above example, we used searched case statement in the WHERE clause to filter the result by getting only data from the emps table for department numbers (50,70).

Oracle Case Statement in WHERE Clause with Parameter Example

We can use a case statement inside the WHERE clause with parameters if we need to apply a conditional clause based on one or more parameters.

SELECT employee_no, name, department_no
FROM   emps
WHERE  (CASE
          WHEN :p_dept_no = 50 THEN 0
          WHEN :p_dept_no = 70 THEN 0
          ELSE -1
        END) = 0;

In the above example, we used searched case statement in the WHERE clause with parameters to filter the result by getting only data from the emps table for department numbers (50,70).

Oracle Case Statement in ORDER BY Clause Example

We can use a case statement inside the ORDER BY clause if we need to apply for conditional ordering.

SELECT   employee_no, name, department_no
FROM     emps
ORDER BY (CASE department_no
            WHEN 80 THEN 'Information Technology'
            WHEN 70 THEN 'Human Resource'
            WHEN 50 THEN 'Accounting'
            WHEN 30 THEN 'Engineering'
          END) DESC;

In the above example, we used a simple case statement in the ORDER BY clause to sort results returned from a query alphabetically in descending order.

Oracle Case Statement in GROUP BY Clause Example

We can use a case statement inside the HAVING clause, which is part of the GROUP BY clause if we need to apply for a conditional having condition.

SELECT   department_no, count (employee_no)
FROM     emps
GROUP BY department_no
HAVING   sum ( (CASE
                  WHEN department_no = 70 THEN salary
                  ELSE 0
                END)) > 10000
OR       sum ( (CASE
                  WHEN department_no = 50 THEN salary
                  ELSE 0
                END)) > 10000;

In the above example, we used searched case statement in the HAVING clause to get only the total salary of more than 10000 when departments are in (70,50).
in other words, we have counted employees only their salary of more than 10000$ in the case of two departments (70,50).

Using Oracle Case Statement Multiple Columns Example

We can use a case statement inside any clause and put multiple columns in one condition; this technique is very handful when we want to implement complex business rules.

SELECT employee_no
      ,name
      ,salary
      , (CASE
           WHEN department_no = 80 AND salary BETWEEN 1000 AND 4000 THEN 'Very Low'
           WHEN department_no = 80 AND salary BETWEEN 4001 AND 8000 THEN 'Low'
           WHEN department_no = 80 AND salary BETWEEN 13500 AND 13999 THEN 'Medium'
           WHEN department_no = 80 AND salary BETWEEN 14000 AND 15000 THEN 'High'
           ELSE 'No range'
         END)
         salary_range
FROM   emps

In the above example, we used the oracle case statement with multiple columns in one condition; In this way, we checked the range for department number 70 and printed the High, Medium, Low, or No range according to certain values.

Similar expressions to the Oracle case statement:

CURSOR: expression returns a nested cursor.
INTERVAL: interval expression yields a value of INTERVAL YEAR TO MONTH or INTERVAL DAY TO SECOND.
EXPRESSION: List other SQL expressions.

Oracle SQL CASE statement Quiz?

Don’t forget to take the following quiz to prove your understanding the SQL CASE statement concept

Start Quiz

Question

Your answer:

Correct answer:

Next

You got {{SCORE_CORRECT}} out of {{SCORE_TOTAL}}

Your Answers

In this tutorial, you have learned how to use the Case expression to use alternative IF-THEN-ELSE logic in SQL. Also, We have given quiz to prove your understanding the SQL CASE statement concept.
If you have a inquiry or doubt don’t hesitate to leave them in comment. we are waiting your feedback as well.

Hopefully, it was clear and concise.