The magical application of decode in ORCL

Before we knew this function, we had to use a number of functions and outer joins to calculate the categorical statistics for the same table or one of the fields in the same query statement block. Using this function can often solve a lot of complex things.

Online A section of the representative decode usage, do not have to do too much explanation, it is at a glance. Of course, his application is more than that.

SelectID, decode ( Sign(Score- -),1,'Excellent',0,'Excellent',-1, Decode ( Sign(Score- -),1,'Good',0,'Good',-1, Decode ( Sign(Score- -),1,'Pass',0,'Pass',-1,'inferior lattice')))  fromStudent

I have encountered a similar scenario in the actual development, to count the value of a field, in the Java background to do the corresponding processing, just at the beginning I used to search for different values and then query:

Selectd.su, A.green, B.yellow, c.red from(Select Count(*) Green fromZx_gzdb_taskwhereWaring= '1') A, (Select Count(*) Yellow fromZx_gzdb_taskwhereWaring= '2') b, (Select Count(*) Red fromZx_gzdb_taskwhereWaring= '3') C, (Select Count(*) Su fromZx_gzdb_task) D;

After using decode:

Select sum(Decode (z.waring,'1','1','0')) Green,sum(Decode (z.waring,'2','1','0')) Yellow,sum(Decode (z.waring,'3','1','0')) Red,Count(*) Su fromZx_gzdb_task Z;

More useful and functional syntax analysis:

1. The Decode function is Oracle PL/SQL is one of the most powerful functions, and only Oracle SQL provides this function, and the SQL implementation of other database vendors does not yet have this function. What is the use of decode? Let's first construct an example, assuming that we want to raise wages for the smart-star staff, the standard is: wages under 8000 yuan will be added 20%, wages in the 8000 yuan plus 15%, the usual practice is to first select the record of the wage field value? Select Salary to Var-salary from employee, and then the variable var-salary is judged by a flow control statement such as If-then-else or choose case. If we use the Decode function, then we can omit these flow control statements, which can be done directly through the SQL statement. The following: Select Decode (salary-8000), 1,salary*1.15,-1,salary*1.2,salary from employee is not very concise?

2. DECODE syntax: DECODE (value,if1,then1,if2,then2,if3,then3,..., else), indicating that if value equals IF1, the result of the DECODE function is returned THEN1,..., Returns else if it is not equal to any of the if values. At first glance, DECODE can only do equals test, but just see, we can use some function or calculation instead of value, it is possible to make the DECODE function more than, less than or equal to the function.

3. Compare size

Select decode (sign (variable 1-variable 2), 1, variable 1, variable 2) from dual; --Take a smaller value

The sign () function returns 0, 1, 1, depending on whether a value is 0, positive, or negative.

For example:

Variable 1=10, variable 2=20

SIGN (variable 1-variable 2) returns the -1,decode decoding result as "Variable 1", which achieves the purpose of taking a smaller value.

Grammar:

The syntax structure of the DECODE function is as follows:
Decode (expression, search_1, result_1)
Decode (expression, search_1, Result_1, search_2, result_2)
Decode (expression, search_1, Result_1, search_2, Result_2, ....., Search_n, Result_n)

Decode (expression, search_1, result_1, default)
Decode (expression, search_1, Result_1, search_2, result_2, default)
Decode (expression, search_1, Result_1, search_2, Result_2, ....., Search_n, result_n, default)

The decode function compares the expression with the search word, returns the result if it matches, returns the default value if it does not match, or returns a null value if no default value is defined.

The magical application of decode in ORCL