Decode () function in SQL

When I read other users' SQL statements today, I still see the decode () function. I have never touched it before. I checked it online. It is quite a useful function, write it down and hope it will help you!

 

 

Decode () function introduction:

Main function: translate the query result into other values (that is, it is expressed in other forms. The following is an example );

Usage:

Select decode (columnname, value 1, translation value 1, value 2, translation value 2,... value n, translation value n, default value)

From talbename

Where...

Columnname is the column defined in the table to be selected,

· Meaning:

Decode (condition, value 1, translation value 1, value 2, translation value 2,... value n, translation value n, default value) is understood as follows:

If (condition = value 1)

Then

Return (translation value 1)

Elsif (condition = value 2)

Then

Return (translation value 2)

......

Elsif (condition = value n)

Then

Return (translation value n)

Else

Return (default)

End if

Note: The default value can be the column name you want to select, or other values you want to define, such as other;

Example:

A table named output is defined, and two columns are defined as monthid (VAR type) and sale (number type). If sale is set to 1000, = 2000 translate to C, = 3000 translate to B, = 4000 translate to a, if other values translate to other;

The SQL statement is as follows:

Select monthid, decode (sale, 1000, 'D', 2000, 'C', 3000, 'B', 4000, 'A', 'other') sale from output

Special cases:

Compare with only one value

Select monthid, decode (sale, null, '---', sale) sale from output

Other functions, such as nvl or sign (), can be used in decode;

Nvl (expr1, expr2)

If expr1 is null, expr2 is returned; otherwise, expr1.

Select name, nvl (to_char (Comm), 'not application') from Table1;

If the decode function is used

Select monthid, decode (nvl (sale, 6000), 6000, 'ng ',' OK ') from output

 

The sign () function returns 0, 1,-1, and,

If a smaller value is

Select monthid, decode (sign (sale-6000),-1, sale, 6000) from output to get a smaller value.