How to use decode in Oracle

Editor to share with you how to use decode in Oracle. I hope you will get something after reading this article. Let's discuss it together.

The grammatical structure is as follows:

Decode (expression, sch_1, res_1)

Decode (expression, sch_1, res_1, sch_2, res_2)

Decode (expression, sch_1, res_1, sch_2, res_2,...., sch_n, res_n)

Decode (expression, sch_1, res_1, default)

Decode (expression, sch_1, res_1, sch_2, res_2, default)

Decode (expression, sch_1, res_1, sch_2, res_2,...., sch_n, res_n, default)

Compares an expression with a search word and returns a result if it matches, a default value if it does not match, or a null value if no default value is defined.

Select name,sub,decode (sub, 'chinese',score,0) from student_score

The function of the decode function is that it can judge not only constant values, but also fields. The above statement:

When the account is chinese, select the value corresponding to the score field instead of the chinese account, and the corresponding score value is 0

Select name

Sum (decode (subject, 'Chinese', nvl (score, 0), 0)) "Ch"

Sum (decode (subject, 'Mathematics', nvl (score, 0), 0)) "Math"

Sum (decode (subject, 'English', nvl (score, 0), 0)) "En"

From xxx group by name;-Row transfer

The decode function can also be used as a condition, such as: where score= decode (subject, 'Chinese',score)

Equivalent to the following case when

Select name

Sum (case when subject='Ch'

Then nvl (score,0)

Else 0

End) "Ch"

Sum (case when subject='Math'

Then nvl (score,0)

Else 0

End) "Math"

Sum (case when subject='En'

Then nvl (score,0)

Else 0

End) "En"

From xxx group by name

After reading this article, I believe you have a certain understanding of "how to use decode in Oracle". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!