How to use oracle case when

Case when usage, simple Case Function
Use a simple CASE expression to determine the return value.

Syntax:

CASE search_expression

WHEN expression1 THEN result1

WHEN expression2 THEN result2

...

WHEN expressionN THEN resultN

ELSE default_result

Search for the CASE expression and use the condition to determine the return value.

Syntax:

CASE

WHEN condition1 THEN result1

WHEN condistion2 THEN result2

...

WHEN condistionN THEN resultN

ELSE default_result

END

Example:

Select product_id, product_type_id,

Case

When product_type_id = 1 then 'book'

When product_type_id = 2 then 'video'

When product_type_id = 3 then 'dvd'

When product_type_id = 4 then 'cd'

Else 'magazine'

End

From products

These two methods can achieve the same function. Simple Case functions are relatively simple in writing, but compared with Case search functions, there are some functional limitations, such as writing Case functions.

Note that the Case function returns only the first value that meets the condition, and the rest of the Case will be automatically ignored.

For example, in the following SQL statement, you will never get the result of the second type.

The Code is as follows:
 

Case when col_1 IN ('A', 'B') then' first class'

WHEN col_1 IN ('A') then' Second Class'

ELSE 'others' END
 

Let's take a look at what we can do with the Case function.

1. Known Data is grouped and analyzed in another way.

There are the following data: (for better understanding, I did not use country code, but directly use the country name as the Primary Key)

Country population (population)

China 600

US 100

Canada 100

UK 200

France 300

Japan 250

Germany 200

Mexico 50

India 250

The population of Asia and North America is counted based on the population data of this country. The following result is returned.

Continent population

Asia 1100

North America 250

Other 700

What do you do if you want to solve this problem? Generating a View with continent Code is a solution, but it is difficult to dynamically change the statistical method.

If the Case function is used, the SQL code is as follows:

Select sum (population ),

CASE country

WHEN 'China' THEN 'Asian'

WHEN 'India 'then' Asia'

WHEN 'Japan 'then' Asia'

WHEN 'American 'then' North American'

WHEN 'Canada 'then' North America'

WHEN 'Mexico 'then' North America'

ELSE 'others' END

FROM Table_A

Group by case country

WHEN 'China' THEN 'Asian'

WHEN 'India 'then' Asia'

WHEN 'Japan 'then' Asia'

WHEN 'American 'then' North American'

WHEN 'Canada 'then' North America'

WHEN 'Mexico 'then' North America'

ELSE 'others' END;

Similarly, we can use this method to determine the wage level and count the number of people at each level. The SQL code is as follows:

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,

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;

2. Use an SQL statement to group different conditions.

The following data is available:

Country sex population (population)

China 340

China 260

US 1 45

US 2 55

Canada 1 51

Canada 2 49

UK 1 40

UK 2 60

Group by country and gender. The result is as follows:

National male

China 340 260

US 45 55

Canada 51 49

UK 40 60

In normal cases, UNION can also be used to query with a statement. However, this will increase the consumption (two Select clauses), and the SQL statement will be relatively long.

The following is an example of using the Case function to complete this function.

The Code is as follows:
SELECT country,

SUM (case when sex = '1' THEN

Population ELSE 0 END), -- male population

SUM (case when sex = '2' THEN

Population ELSE 0 END) -- female population

FROM Table_A

Group by country;

In this way, Select is used to complete the output form of the explain table, which fully shows the power of the Case function.

3. Use the Case function in Check.

Using the Case function in Check is a good solution in many cases. There may be a lot of people who don't need Check at all, so I suggest you try to use Check in SQL after reading the example below.

The following is an example.

Company A, the company has A rule that the wages of female employees must be higher than 1000 yuan. If you use Check and Case

The Code is as follows:
CONSTRAINT check_salary CHECK

(Case when sex = '2'

Then case when salary> 1000

THEN 1 ELSE 0 END

ELSE 0 END)

If you simply use Check, as shown below:

The Code is as follows:

CONSTRAINT check_salary CHECK

(Sex = 's2' AND salary> 1000)

The conditions of the female employee are met, and the male employee cannot enter the information.

Instance

The Code is as follows:

Create table feng_test (id number, val varchar2 (20 );

Insert into feng_test (id, val) values (1, 'abcde ');
Insert into feng_test (id, val) values (2, 'abc ');
Commit;

SQL> select * from feng_test;

Id val
-------------------
1 abcde
2 abc

SQL> select id
, Case when val like 'a % 'then' 1'
When val like 'abcd % 'then' 2'
Else '000000'
End case
From feng_test;

Id case
---------------------
1 1
2 1
 

Based on my own experience, I think that the use of case when is similar to asp case when and the use of off statements in php swicth case development, as long as I know the basics, I think the case when in SQL is actually quite understandable.