Description: DECODE (condition, value 1, translation value 1, value 2, translation value 2 ,... value n, translation value n, default value) the meaning of this function is as follows: IF condition value 1 THENRETURN (translation value 1) ELSIF condition value 2 THENRETURN (translation value 2 )...... ELSIF condition value nTHENRETURN (translation value n) ELSERETURN (default value) ENDIF usage: 1
Description: DECODE (condition, value 1, translation value 1, value 2, translation value 2 ,... value n, translation value n, default value) the meaning of this 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 usage: 1
Description:
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
Select decode (sign (variable 1-variable 2),-1, variable 1, variable 2) from dual; -- smaller value
Select decode (sign (variable 1-variable 2), 1 or 0, variable 1, variable 2) from dual; -- greater
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, and the decode decoding result is "variable 1", achieving the goal of getting a smaller value.
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', substring, 0 )),
Sum (decode (substrb (month, 5, 2), '02', substring, 0 )),
Sum (decode (substrb (month, 5, 2), '03', substring, 0 )),
Sum (decode (substrb (month, 5, 2), '04 ', hour, 0 )),
Sum (decode (substrb (month, 5, 2), '05 ', substring, 0 )),
Sum (decode (substrb (month, 5, 2), '06', substring, 0 )),
Sum (decode (substrb (month, 5, 2), '07 ', substring, 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', substring, 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 );
Take the value of 2 bytes (substrb part) from the first byte in month );
Then judge the value. If the value is equal to '01', take the value of the values column; otherwise, take the value 0 (decode part );
Then sum the sum ).
In Oracle, there are two functions, namely substrb and substr, which have different meanings. One is based on characters, and the other is based on bytes.
