Oracle case when usage

Oracle case when usage ----- This article is prepared by netizens, 1. The case when expression has 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 select case when usage 

SELECT grade, COUNT (case when sex = 1 THEN 1/* sex 1 for boys, 2 girls */
ELSE NULL
END) boys,
COUNT (case when 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 usageSELECT

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 Name
COUNT (*)
FROM Table_A
GROUP
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. For other IF-THEN-ELSE implementations, the 3.1 DECODE () function

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

It seems that only Oracle provides this function, and ansi SQL is not supported, and the syntax is not CASE WHEN clear, which is not recommended for personal use.

3.2 Special implementation in the WHERE clause

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 in special cases. pay more attention to the logic and avoid mistakes.