Techniques for using decode () functions in Oracle

decode|oracle| Function | Tips decode () skills of using functions
• Software Environment:
1. Windows nt4.0+oracle 8.0.4
2, the Oracle installation path is: C:\ORANT
• meaning Explanation:
Decode (condition, value 1, translation value 1, value 2, translation value 2,... Value n, translation value n, default value)

The meaning of the function is 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 value)
End IF
· 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, and 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 the -1,decode decoding result as "Variable 1", which achieves the goal of taking a smaller value.


2, table, view structure transformation
There is an existing sales table sale, the table structure is:
Month char (6)--month
Sell number (10,2)--monthly sales Amount

The existing data is:
200001 1000
200002 1100
200003 1200
200004 1300
200005 1400
200006 1500
200007 1600
200101 1100
200202 1200
200301 1300

Data that you want to translate into the following structure:
Year char (4)--years
Month1 Number (10,2)--January Sales Amount
Month2 Number (10,2)--February Sales Amount
Month3 Number (10,2)--March Sales Amount
Month4 Number (10,2)--April Sales Amount
Month5 Number (10,2)--May Sales Amount
Month6 Number (10,2)--June Sales Amount
Month7 Number (10,2)--July Sales Amount
Month8 Number (10,2)--August Sales Amount
Month9 Number (10,2)--September Sales Amount
Month10 Number (10,2)--October Sales Amount
Month11 Number (10,2)--November Sales Amount
Month12 Number (10,2)--December Sales Amount

The SQL statement for structural transformation is:
Create or Replace view
V_sale (YEAR,MONTH1,MONTH2,MONTH3,MONTH4,MONTH5,MONTH6,MONTH7,MONTH8,MONTH9,MONTH10,MONTH11,MONTH12)
As
Select
SUBSTRB (month,1,4),
Sum (SUBSTRB (month,5,2), ' decode ', sell,0)),
Sum (SUBSTRB (month,5,2), ' decode ', sell,0)),
Sum (SUBSTRB (month,5,2), ' decode ', sell,0)),
Sum (SUBSTRB (month,5,2), ' decode ', sell,0)),
Sum (SUBSTRB (month,5,2), ' decode ', sell,0)),
Sum (SUBSTRB (month,5,2), ' decode ', sell,0)),
SUM (Decode (SUBSTRB (month,5,2), ' modified ', sell,0)),
Sum (SUBSTRB (month,5,2), ' decode ', sell,0)),
Sum (SUBSTRB (month,5,2), ' decode ', sell,0)),
SUM (Decode (SUBSTRB (month,5,2), ' Ten ', sell,0)),
SUM (Decode (SUBSTRB (month,5,2), ' one ', sell,0)),
Sum (SUBSTRB (month,5,2), ' decode ', sell,0)
From sale
Group by SUBSTRB (month,1,4);