Detailed Explanation of the Usage of oracle case when Statement

1. CASE WHEN expressions have two forms

-- Simple Case Function   
CASE sex  
WHEN '1' THEN ' Male '  
WHEN '2' THEN ' Female '  
ELSE ' Others ' END  
--Case Search function   
CASE
WHEN sex = '1' THEN ' Male '  
WHEN sex = '2' THEN ' Female '  
ELSE ' Others ' END  

2. Usage of CASE WHEN in different positions in the statement
2.1 Usage of SELECT CASE WHEN

SELECT   grade, COUNT (CASE WHEN sex = 1 THEN 1      /*sex 1 For boys, 2 Girls */
                       ELSE NULL
                       END)  Number of boys ,
                COUNT (CASE WHEN sex = 2 THEN 1
                       ELSE NULL
                       END)  Number of girls 
    FROM students GROUP BY grade;

2.2 Usage of WHERE CASE WHEN

SELECT T2.*, T1.*
   FROM T1, T2
  WHERE (CASE WHEN T2.COMPARE_TYPE = 'A' AND
                   T1.SOME_TYPE LIKE 'NOTHING%'
                THEN 1
              WHEN T2.COMPARE_TYPE != 'A' AND
                   T1.SOME_TYPE NOT LIKE 'NOTHING%'
                THEN 1
              ELSE 0
           END) = 1

2.3 Usage of GROUP BY CASE WHEN

SELECT  
CASE WHEN salary <= 500 THEN '1'  
WHEN salary > 500 AND salary <= 600  THEN '2'  
WHEN salary > 600 AND salary <= 800  THEN '3'  
WHEN salary > 800 AND salary <= 1000 THEN '4'  
ELSE NULL END salary_class, --  Alias naming 
COUNT(*)  
FROM    Table_A  
GROUP BY  
CASE WHEN salary <= 500 THEN '1'  
WHEN salary > 500 AND salary <= 600  THEN '2'  
WHEN salary > 600 AND salary <= 800  THEN '3'  
WHEN salary > 800 AND salary <= 1000 THEN '4'  
ELSE NULL END;  

3. Other implementations of IF-THEN-ELSE
3.1 DECODE () Function

select decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown')
from   employees;

It seems that only Oracle provides this function, and does not support ANSI SQL, and its syntax is not as clear as CASE WHEN, so it is not recommended for personal use.
3.2 Special Implementation in WHERE

SELECT T2.*, T1.*
   FROM T1, T2
  WHERE (T2.COMPARE_TYPE = 'A' AND T1.SOME_TYPE LIKE 'NOTHING%')
         OR
        (T2.COMPARE_TYPE != 'A' AND T1.SOME_TYPE NOT LIKE 'NOTHING%')

This method is also used under special circumstances, so pay more attention to logic and make no mistakes.