The Oracle CASE Statement

As per the oracle definition for a CASE Statement, from any of the many sites The CASE statement chooses from a sequence of conditions and executes the corresponding statement. The CASE statement evaluates multiple BOOLEAN expressions and chooses the first one whose value is TRUE. Below link

Get started with Oracle Database Development

The CASE statement chooses from a sequence of conditions and runs a corresponding statement. The simple CASE statement evaluates a single expression and compares it to several potential values. The searched CASE statement evaluates multiple Boolean expressions and chooses the first one whose value is TRUE. Below link

The Oracle Case Statement

Below is a simple example for using a CASE statement to select from the employees table based on the hire_date. 

In below example 

All employees hired before 01-Jan-1990 get a 15% increment, 

Employees hired between 01-Jan-1990 and 01-Jan-1995 get a 10% increment, 

Employees hired between 01-Jan-1995 and 01-Jan-2000 get a 5% increment

SELECT LAST_NAME "Name", HIRE_DATE "Started", SALARY "Salary", 
CASE WHEN HIRE_DATE < TO_DATE('01-Jan-90', 'dd-mon-yy') 
  THEN TRUNC(SALARY*1.15, 0) 
WHEN HIRE_DATE < TO_DATE('01-Jan-95', 'dd-mon-yy') 
  THEN TRUNC(SALARY*1.10, 0) 
WHEN HIRE_DATE < TO_DATE('01-Jan-00', 'dd-mon-yy') 
  THEN TRUNC(SALARY*1.05, 0) 
ELSE SALARY END "Proposed Salary" 
FROM EMPLOYEES WHERE DEPARTMENT_ID = 100 ORDER BY HIRE_DATE;

The below statement holds true for the following sample query 

The searched CASE statement evaluates multiple Boolean expressions and chooses the first one whose value is TRUE.

SELECT
CASE
WHEN '01'= '00'                        then 'A'
    WHEN '01' ='01' AND 'PXWeb' = 'PXWeb' then 'B'
    WHEN '01' ='02'                      then 'C'
    WHEN '01' ='03'                        then 'D'
    WHEN '01' ='04'                      then 'E'
    WHEN '01' ='05'                      then 'F'
    WHEN '01' ='06'                      then 'G'
END    
FROM DUAL;

Output of this is B

If the first CASE statement evaluates to true I get the output as A

SELECT
CASE
WHEN '01'= '01'                        then 'A'
    WHEN '01' ='01' AND 'PXWeb' = 'PXWeb' then 'B'
    WHEN '01' ='02'                      then 'C'
    WHEN '01' ='03'                        then 'D'
    WHEN '01' ='04'                      then 'E'
    WHEN '01' ='05'                      then 'F'
    WHEN '01' ='6'                      then 'G'
END    
FROM DUAL; 

Output of this is

A

With the example below, the output I get changes with the ORDER of the CASE statement. 

Am I doing something wrong here?

In the below scenario,

I am going to create an ORDER table and prioritize the ORDERS as follows

If the bill_date is '03-NOV-24' , I want to select all records from the table where the priority is FRST

If the buy_date <> bill_date, I want to select records where the priority is THRD

In all other cases I want to select records where priority is SCND.

My goal is to select only the records which satisfy the first condition which holds true and give that output based on the Oracle definition for CASE

DROP TABLE INVOICE;
CREATE TABLE INVOICE
(
  ID       NUMBER,
  SRL_NUM  NUMBER,
  BUY_DATE      DATE,
  PRIORITY   VARCHAR2(100),
  BILL_DATE     DATE,
  AMT        NUMBER
);

DELETE FROM INVOICE WHERE ID = 100002;

INSERT INTO INVOICE VALUES(100002, 1, '01-FEB-23', NULL, '03-FEB-23',1000);
INSERT INTO INVOICE VALUES(100002, 2, '01-MAY-23', NULL, '03-MAY-23',2000);
INSERT INTO INVOICE VALUES(100002, 3, '01-AUG-23', NULL, '03-AUG-23',3000);
INSERT INTO INVOICE VALUES(100002, 4, '01-NOV-23', 'THRD','03-NOV-23',4000);
INSERT INTO INVOICE VALUES(100002, 5, '01-FEB-24', 'SCND','01-FEB-24',5000);
INSERT INTO INVOICE VALUES(100002, 6, '01-MAY-24', 'SCND','01-MAY-24',6000);
INSERT INTO INVOICE VALUES(100002, 7, '01-AUG-24', 'SCND', '01-AUG-24',7000);
INSERT INTO INVOICE VALUES(100002, 8, '01-NOV-24', 'FRST', '03-NOV-24',8000);

When I write the select query with the CASE as per the order below, I do not get the record where the PRIORITY is FRST. I only get the records with THRD and SCND priority.

SELECT id, srl_num, PRIORITY
FROM
INVOICE
WHERE ID = 100002
AND PRIORITY = 
  CASE
  WHEN
    BUY_DATE <> BILL_DATE
    THEN 'THRD'
  WHEN BILL_DATE = '03-NOV-24'
    THEN 'FRST'
  ELSE
     'SCND'
END;

100002 4 THRD 

100002 5 SCND 

100002 6 SCND 

100002 7 SCND 

But if I change the order of the CASE statement as below I get all the records as required.

SELECT id, srl_num, PRIORITY
FROM
INVOICE
WHERE ID = 100002
AND PRIORITY = 
  CASE
  WHEN BILL_DATE = '03-NOV-24'
    THEN 'FRST'
  WHEN
    BUY_DATE <> BILL_DATE
    THEN 'THRD'
  ELSE
     'SCND'
END;

100002 4 THRD 

100002 5 SCND 

100002 6 SCND 

100002 7 SCND 

100002 8 FRST 

I would like to know how changing the order of my CASE statement is changing the output that I get.

Thanks in advance.

Thought for the day

We don't have a great day. We make it a great day

--Frosty Westering