Case in oracle and Its similar usage

In actual data, in order to display the information in the table more intuitively, values of the same type or within a certain range are usually classified as one type, and the 'old value' is replaced by a unified 'new value ', at this time, we will use the case statement. Let's take a look at several usage cases.
1. Simple case statement
The usage of a simple case statement is similar to that of switch case in C #. case capture customization. After then, a new value is returned ', this syntax can also be used to determine whether a specified field has a specified value. The following example shows its usage: 1 select (case acct_month 2 when '000000' then 3' June '4 when '000000' then 5' 07-July 6 else www.2cto.com 7' other years '8 end) year, 9 svcnum, 10 fee1, 11 fee2, 12 fee313 from TRAIN_TAB_BILLDETAIL; in this example, if the value of acct_month is 200706, in reality, replace the original value with the value '2014, June '. When the acct_month value does not meet any case conditions, run the else statement and replace the original value with the data value after else. However, you must note that such a simple case statement cannot use any comparison operator. At the same time, the data after when and then must have the same data type as acct_month or can be implicitly converted, otherwise, an error is returned. Www.2cto.com
2. case search statement
Case search statement and if... The usage of the else statement is similar. Conditional search is performed to return the value that meets the condition. In case... When... Then... In the else syntax, when a comparison condition is used, the return value of boolean is true or false. If false is returned, the explain when statement is executed until the comparison result is true, return the value after the sentence then to replace the original value. If none of the when conditions are met, the system jumps to else and returns the matching value after else, the return value type must be the same as the value type of the compare field or can be converted by implicit type conversion. Otherwise, an error is returned.
See the following example:
Return data in the specified output format from the following three tables:



The output shows 'prefecture (areadef) ', 'total score (fee1 + fee2 + fee3)', 'number of users (subscrbid) ', and 'total revenue (fee1 + fee2 + fee3 )', the following code shows the cities in the select region. The total cost is divided into different files, the count (*) number of accounts, and the sum (total revenue) revenue is from (select areadef region, (case when fee1 + fee2 + fee3> = 0 and fee1 + fee2 + fee3 <10 then 'is greater than or equal to 0, less than 10 'when fee1 + fee2 + fee3> = 10 and fee1 + fee2 + fee3 <100 then' greater than or equal to 10, less than 100 'else' greater than or equal to 100 'end) the total fee is divided into two parts: primary, fee1 + fee2 + fee3 total revenue from TRAIN_TAB_AREA, TRAIN_TAB_SUBSCRBPRCPLN, TRAIN_TAB_BILLDETAIL where primary = primary and secondary = TRAIN_TAB_AREA.Areaid, the total fee is divided into www.2cto.com order by sum (total income). The query result is

In this example, you want to use the table connection statistics to show the 'total expense files', which is a data field displayed for determining the total expense quota, and then grouping by grouping function, the result that you want to see is displayed by the statistical group. It has the advantage of 'overall view' compared with the result that is not grouped.
3. similar to NULLIF (a, B), NULLIF (a, B) is easy to use. If the value of parameter a is equal to the given value B, null is returned, and the value of a is returned () the usage is similar. COALESCE (a, B) returns value B when the value of parameter a is null. Otherwise, return value. No specific examples are provided here. Author Rigwarl. Z