Decode () function instructions in oracle

Description of the Decode () function in oracle: 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. Compare the size of select decode (sign (variable 1-variable 2 ), -1, variable 1, variable 2) from dual; -- take a smaller value www.2cto.com select decode (sign (variable 1-variable 2), 1 or 0, variable 1, variable 2) from dual; -- returns 0, 1, and-1 for the greater sign () function based on whether a value is 0, positive, or negative. For example, if the variable 1 is 10, variable 2 = 20 s Ign (variable 1-variable 2) returns-1, and the decode decoding result is "variable 1", achieving the goal of getting a small value. 2. the table and view structure are converted to the existing product sales table sale. The table structure is month char (6) -- month sale number () -- month sale amount existing data is: 200001 1000 200002 1100 200003 1200 200004 1300 200005 1400 200006 1500 200007 1600 200101 1100 200202 1200 200301 1300 www.2cto.com data to be converted to the following structure: year char (4) -- year month1 number () -- January sales amount mon2number () -- February sales amount month3 number () -- March sales amount month4 number) -- sales amount month5 number () -- sales amount month6 Number () -- June sales amount month7 number () -- July sales amount month8 number () -- August sales amount month9 number () -- September sales amount month10 number) -- October sales amount month11 number () -- November sales amount month12 number () -- SQL statement for December sales amount Structure Conversion: create or replace view v_sale (year, month1, month2, month3, month4, month5, month6, month7, month8, month9, month10, month11, month12) as select substrb (month,), sum (decode (substrb (month, 5, 2 ),' 01 ', hour, 0), sum (decode (substrb (month, 5, 2), '02', hour, 0), sum (decode (substrb (month, 5, 2), '03', hour, 0), sum (decode (substrb (month, 5, 2), '04 ', hour, 0 )), sum (decode (substrb (month, 5, 2), '05 ', clerk, 0), sum (decode (substrb (month, 5, 2), '06', clerk, 0), sum (decode (substrb (month, 5, 2), '07 ', clerk, 0), sum (decode (substrb (month, 5, 2), '08 ', second, 0), sum (decode (substrb (month, 5, 2), '09', second, 0), sum (decode (substrb (month, 5), 2), '10', period, 0), sum (decode (substrb (month, 5, 2), '11', period, 0 )), sum (decode (substrb (month,), '12', region, 0) from sale www.2cto.com group by substrb (month ); take the value of 2 bytes (substrb part) from the first byte in month, and 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) and sum the sum (sum part ). 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.