Easy-to-use functions sign and decode

Source: Internet
Author: User

Today, we encountered a problem. We need to compare the size of a field with 5. The output value greater than 5 is 0, and the output value smaller than 5 is 1. If PLSQL is used for programming, you can use the logic such as if/else for judgment, but it cannot be written like this in SQL. After being recommended by some netizens, I used sign and decode in combination, which worked very well,CodeSmall fresh.

The Code is as follows:

Decode (Sign(Trunc (col1-Col2)- 5),1,0,1) Ontime_flag

In this way, you can.

The sign function is used as follows:

If the sign parameter is negative,-1 is output. If it is positive, 1 is output. If it is 0, 0 is output. In this way, with decode, you can calculate and output the data according to my ideas.

In addition, the sign is provided by Oracle, and its efficiency should be higher than the logic written by itself. If you write the logic by yourself, there should be an IF/else. The amount of code is also increased, and the code written like me is still relatively small and fresh. I hope to help you.

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.