Introduction to the Decode () function in SQL _mssql

The Decode function is a unique method of computing provided by Oracle's SQL software Oracle Pl/sql, known for its concise operations, controllable data models, and flexible format conversions.

Look at other people's SQL today to see that there are decode () function, has never been contacted, the Internet check, but also quite good use of a function, write down hope to friends have help ha!

Introduction to the Decode () function:

Main role: Translate the results of the query into other values (that is, in other forms, as illustrated below);

How to use:

Select decode (ColumnName, value 1, translation value 1, value 2, translation value 2,... Value n, translation value n, default value) from
talbename
Where ...

Where ColumnName is the column defined in the table to be selected,

• meaning Explanation:

Decode (condition, value 1, translation value 1, value 2, translation value 2,... The 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 (conditional = = value N)
then return
(translated value n)
else return
(default) End
if

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

An example is provided:

The definition of a table named output, which defines two column is Monthid (VAR) and sale (number type), if the sale value =1000 when translated to d,=2000 when translated to c,=3000 when translated as a , if other values are translated into other;

SQL is as follows:

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

Special case:

If you compare to only one value

Select Monthid, decode (sale, NULL, '---', sale) sale from output

Another: Decode can use other functions, such as NVL function or sign () function, etc.

NVL (EXPR1,EXPR2)

If EXPR1 is null, return EXPR2 or return EXPR1.

SELECT Name,nvl (To_char (COMM), ' not application ') from TABLE1;

If you use the Decode function, it's

The sign () function returns 0, 1, and 1, depending on whether a value is 0, positive, or negative.

If you take a smaller value, you are

Select Monthid,decode (sign (sale-6000), -1,sale,6000) from output, that is, to achieve a smaller value.

Summary:

Action: Compares an input value to a parameter list in a function and returns a corresponding value based on the input value. The parameter list of a function is a number of sequential pairs of numbers and their corresponding result values. Of course, the function also has a default return value if it fails to match any of the actual parameter pairs successfully. Other functions that differ from SQL, the Decode function can also recognize and manipulate null values.

Syntax: DECODE (CONTROL_VALUE,VALUE1,RESULT1[,VALUE2,RESULT2 ...) [, Default_result]);

The value that the control _value attempted to process. The decode function compares this value to a sequence of subsequent sequences to determine the return value. A value1 is a numerical value that consists of a sequence pair. If the input value matches successfully, the corresponding result is returned. corresponding to an empty return value, you can use the key word NULL in the corresponding RESULT1 is a constituent sequence of the result value. The default value that the function returns when Default_result fails to match any of the values.