How to use ORACLE case when

We all know the usage of case when. Once a certain when is satisfied, the data will exit the case when instead of considering other cases,ArticleTo introduce the usage of case when in detail and give an example.

Case when usage-simple case function simple case expression, use the expression to determine the return value. Syntax: www.2cto.com Code The following case search_expression when expression1 then result1 when expression2 then result2... when expressionn then resultn else default_result searches for the case expression and uses the condition to determine the return value. syntax: the code is as follows: 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 'Vi Deo 'when product_type_id = 3 then' DVD 'when product_type_id = 4 then 'CD 'else' magazine 'www.2cto.com end from products can implement the same functions. 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 code of "Class 2": case when col_1 in ('A', 'B ') then 'first class 'when col_1 in ('A') then' Second Class 'else' others'. Next let's take a look at what the case function can do. 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 (country) Population (Population) china 600 U.S. 100 Canada 100 UK 200 France 300 Japan 250 Germany 200 Mexico 50 India 250 according to the population data of this country, the population of Asia and North America is counted. The following result is returned. Continent population: Asia: 1100 North America: 250 others: 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 at www.2cto.com, the SQL code is as follows: Select sum (Population ), case countrywhen 'China' then' Asia 'when' India 'then' Asia 'when' Japan 'then' Asia 'when' USA 'then' North America 'when' Canada 'then' North America 'when' Mexico 'then' North America 'else' miscellaneous 'endfrom table_agroup by case countrywhen' China 'then' Asia 'when' India 'then' Asia 'when' Japan 'then' Asia 'when' 'then' North America '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 calculate Number of students. The SQL code is as follows: selectcase 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_agroup bycase 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; www.2cto.com 2. Use an SQL statement to group different conditions. Country sex population (Population) china 1 340 China 2 260 US 1 45 US 2 55 Canada 1 51 Canada 2 49 UK 1 40 UK 2 60 grouped by country and gender, the result is as follows: male, China, 340, 260, United States, 45, 55, Canada, 51, 49, United Kingdom, 40, 60, and so on. In normal cases, union can also be used to query by a statement. However, this will increase the consumption (two select clauses), and the SQL statement will be relatively long. The following is an example code that uses the case function to complete this function: Select Country, sum (case when sex = '1' thenpopulation else 0 end ), -- male population sum (case when sex = '2' thenpopulation else 0 end) -- female population from table_agroup by country; then we use select to complete the output form of the orders table, this fully demonstrates 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 of Company A, which stipulates that the salary of a female employee must be higher than 1000 RMB. If you use check and case, the Code www.2cto.com is as follows: constraint check_salary check (case when sex = '2' then case when salary> 1000 then 1 else 0 endelse 0 end) if you simply use check, the code shown below is constraint check_salary check (sex = '2' and salary> 1000) where the conditions of the female staff are met, and the male staff cannot enter the information. The instance 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 abcde2 abcsql> select ID, case when Val like 'a % 'then' 1 'when Val like 'abcd % 'then' 2 'else' 999 'www.2cto.com end casefrom feng_test; ID case ----------------------- 1 12 1 based on my own experience, I think I am using case When is similar to ASP case when, which is used to develop the off-statement in PHP swicth case. As long as I know the basics, I think the case when in SQL is actually quite understandable. This article is from yijucheng.