Decode of Oracle

· Software environment:
1. Windows NT4.0 + Oracle 8.0.4
2. Oracle installation path: C: \ orant
· Meaning:
Decode (Condition, value 1, translation value 1, value 2, translation value 2,... value n, translation value n, default value)

The function has the following meanings:
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
· Usage:
1. Compare the size
SelectDecode(Sign (variable 1-variable 2),-1, variable 1, variable 2) from dual; -- smaller value
The sign () function returns 0, 1, and-1 respectively based on a value of 0, positive, or negative.

For example:
Variable 1 = 10, variable 2 = 20
Then sign (variable 1-variable 2) returns-1,DecodeThe decoded result is "variable 1", so that a smaller value is obtained.

2. Table and View Structure Conversion
There is a sales table sale with the following structure:
Month char (6) -- month
Billing number () -- 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 to be converted to the following structure:
Year char (4) -- year
Month1 number () -- sales amount in
Mon2number (February) -- sales amount in December
Month3 number (March) -- sales amount in December
Month4 number (April) -- sales amount in December
Month5 number (May) -- sales amount in December
Month6 number (June) -- sales amount in December
Month7 number (July) -- sales amount in December
Month8 number (August) -- sales amount in December
Month9 number (September) -- sales amount in December
Month10 number (October) -- sales amount in December
Month11 number (November) -- sales amount in December
Month12 number (December) -- sales amount in December

The SQL statement for structure conversion is:
Create or replace View
V_sale (year, month1, mon2, month3, month4, month5, month6, month7, month8, month9, month10, month11, month12)
As
Select
Substrb (month, 1, 4 ),
Sum ( Decode (Substrb (month, 5, 2), '01', month, 0 )),
Sum ( Decode (Substrb (month, 5, 2), '02', month, 0 )),
Sum ( Decode (Substrb (month, 5, 2), '03', hour, 0 )),
Sum ( Decode (Substrb (month, 5, 2), '04 ', month, 0 )),
Sum ( Decode (Substrb (month, 5, 2), '05 ', month, 0 )),
Sum ( Decode (Substrb (month, 5, 2), '06', month, 0 )),
Sum ( Decode (Substrb (month, 5, 2), '07 ', month, 0 )),
Sum ( Decode (Substrb (month, 5, 2), '08 ', hour, 0 )),
Sum ( Decode (Substrb (month, 5, 2), '09', hour, 0 )),
Sum ( Decode (Substrb (month, 5, 2), '10', hour, 0 )),
Sum ( Decode (Substrb (month, 5, 2), '11', hour, 0 )),
Sum ( Decode (Substrb (month, 5, 2), '12', hour, 0 ))
From sale
Group by substrb (month, 1, 4 );