What is the difference between decode and case when?

Source: Internet
Author: User

What is the difference between decode and case when?
Decode can only be equivalent, case when can be used for range, range can be used for condition differentiation, decode can do, and case when can also do; in terms of efficiency, I personally think the efficiency of the two is almost the same; however, the decode syntax is simpler, but it can only be used for equivalent comparison. case when end can be used to determine conditions.

 

CASE When in Oracle:

SELECT LogID, UserID, LogTime, Url, Description ,(
Case
When OperateType = 0 then 'add'
When OperateType = 1 then 'modify'
Else 'delete'
End) OperationName
FROM LOG

Decode () in Oracle ():
· Meaning:
Decode (condition, value 1, translation value 1, value 2, translation value 2,... value n, translation value n, default value)

SELECT LogID, UserID, LogTime, Url, Description,
Decode (OperateType, 0, 'add', 1, 'modify', 'delete') OperationName
FROM LOG

Difference: the two are similar in fixed comparison conditions, such as OperateType and fixed values 0, 1, and 2. However, when the comparison conditions are not fixed, for example, OperateType is changed to Sarlary, when the range is 0-,-or above, it is obviously easier to use case when.

 

 

In fact, decode and case when can often be equivalent. The choice of decode can be determined by the programmer's habits and proficiency. However, it is worth noting that the two forms of CaseWhen are listed as follows. I think the best analysis method is to write an SQL to test it. I will not talk about it here.

 

CASE ExprWHEN Comparison_expr1THEN Return_expr1[WHEN Comparison_expr2THEN Return_expr2WHEN Comparison_exprnTHEN Return_exprnELSE Else_expr] END (a variant of case when) CASEWHEN Comparison_expr1THEN Return_expr1[WHEN Comparison_expr2THEN Return_expr2WHEN Comparison_exprnTHEN Return_exprnELSE Else_expr] End decode ( Col | expression, search1, result1[ , Search2, result2 ,...,] [ , Searchn, resultn ,...,] [ , Default])

--------------------------------------------------------------------------------

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

--------------------------------------------------------------------------------

Related Article

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.