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. 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, 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% ')

This method is also used in special cases, to pay more attention to the logic, do not mistake.

Reprint: http://www.cnblogs.com/eshizhan/archive/2012/04/06/2435493.html

Oracle Case When usage introduction