Decode function usage in Oracle

Meaning explanation:

Decode (condition, value 1, return value 1, value 2, return value 2,... ) Value N, return value N, default value)

The function has the following meanings:

IF condition = value 1 Then

return value 1

ELSEIF condition = value 2 Then

return value 2

     .....

ELSEIF condition = value n Then

return value N

ELSE

RETURN Default Value

END IF

decode (field or Field operation, value 1, value 2, value 3)

The result of this function is that when the value of the field or Field Operation equals the value 1 o'clock, the function returns a value of 2, otherwise the return value 3

Of course, a value of 1, a value of 2, a value of 3 can also be an expression, which makes some SQL statements much simpler.

How to use:

1. 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

Then sign (variable 1-variable 2) returns-1, and thedecode decoding result is "Variable 1", which achieves the purpose of taking a small value.

2, this function is used in the SQL statement , the function is described as follows:

The Decode function is similar to a series of nested if-then-else statements. Base_exp and Compare1,compare2 and so on are compared in turn. If the base_exp and the compare are matched, the corresponding value of I is returned. If the base_exp does not match any of the compare values, the default is returned. Each compare value is evaluated sequentially, and if a match is found, the remaining compare values (if any) are no longer evaluated. A null base_exp is considered equivalent to the null compare value. If required, each compare value is converted to the same data type sing Woo the first compare value, which is also the type of the return value.

The Decode function is very useful in practical development.

Combining the Lpad function, how to make the value of the primary key automatically add 1 and 0 in front
Select Lpad(Decode (count (record number), 0,1,max (To_number (record number) +1)), 14, ' 0 ') record number from Tetdmis

eg

Select Decode (dir,1,0,1) from A1_interval

Dir has a value of 1 to 0 and 0 to 1.

For example, I want to inquire about the number of boys and girls in a class, what is the difference?

Usually we write this:

Select COUNT (*) from table where gender = male;

Select COUNT (*) from table where sex = female;

If you want to show it together, it'll be a union, too much trouble.

With decode, you just need a word.

Select SUM (decode (gender, Male, 1,0)), SUM (decode (gender, female, 1,0)) from table

eg

Select SUM (Decode (Siteno, ' LT ', 1,0)), SUM (Decode (Siteno, ' SZ ', 1,0)) from facd605;

Select SUM (case Siteno if ' LT ' then 1 else 0 end), sum (case Siteno if ' SZ ' then 1 else 0 end) from facd605;

Reference Source: https://www.cnblogs.com/vinsonLu/p/3512526.html

Decode function usage in Oracle