Oracle Case Usage

Oracle Case When usage introduction 1. Case-When expressions have two forms

--Simple Case function  

Case Sex  
When ' 1 ' Then ' men '  
When ' 2 ' then ' women '  
Else ' other ' END  

--case search function  

Case
When sex = ' 1 ' Then ' man '  
When sex = ' 2 ' Then ' women '  
Else ' other ' END  

2. When a case is used in a different position in a statement 2.1 SELECT case when usage

SELECT   grade, COUNT (case if sex = 1 then 1      /*sex 1 for boys, 2 girls */
                       ELSE NULL
                       END) Number of boys,
                COUNT (case if sex = 2 then 1
                       ELSE NULL
                       END) Number of girls
    From students GROUP by grade;

2.3 WHERE case when usage

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.4 GROUP by case when usage

SELECT  
Case when salary <= ' 1 '  
When salary > Salary <=  2 '  
When salary > Salary <=  3 '  
When salary > Salary <= "4"  
ELSE NULL END Salary_class,--alias name
COUNT (*)  
From    table_a  
GROUP by  
Case when salary <= ' 1 '  
When salary > Salary <=  2 '  
When salary > Salary <=  3 '  
When salary > Salary <= "4"  
ELSE NULL END;  

3. Aboutother implementations of If-then-else3.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, there is no syntax when the case is clear, not recommended for personal use.

3.2 Special implementations 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% ')

Oracle Case Usage