Shulou(Shulou.com)06/01 Report--

Flow control function DECODE

Introduction to the decode () function:

Main functions:

Translate the query results into other values (that is, in other forms, as illustrated by the following example)

How to use it:

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

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 (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 choose, or other values you want to define, such as Other, etc.

Examples are as follows:

A table is now defined as output, in which two column are defined as monthid (vartype) and sale (numbered type). If the value of salt is 1000, it is translated into DMagie = 2000, it is translated into C Magi = 3000, it is translated into A when BMagne = 4000, and if other values are translated into Other.

SQL is as follows:

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

Special circumstances:

If you compare with only one value

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

Other functions can be used in decode, such as nvl function or sign () function, etc.

Compare the size function sign

Function syntax:

Sign (n)

Function description:

Take the symbol of the number n, greater than 0 returns 1, less than 0 returns-1, equals 0 returns 0

Example:

1. Select sign (100), sign (- 100), sign (0) from dual

SIGN (100) SIGN (- 100) SIGN (0)

1-1 0

2. Axiom 10, dint 20

Then sign (aMub) returns-1

NVL (EXPR1,EXPR2)

If EXPR1 is NULL, return EXPR2, otherwise return EXPR1.

SELECT NAME,NVL (TO_CHAR (COMM), 'NOT APPLICATION') FROM TABLE1

If you use the decode function, that is

Select monthid,decode (nvl (sale,6000), 6000 from output)

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

If you take a smaller value, it is.

Select monthid,decode (sign (sale-6000),-1 select monthid,decode (sale-6000)) from output achieves the purpose of taking a smaller value.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.