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)),