The magical application of decode in ORCL

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.