Weird DECODE Function

The strange DECODE function encountered the following problem today: www.2cto.com order by decode (column_id, 1, null, 2, null, 3, null, column_id, that is, when the number of columns is greater than 10, the column_id order is 10, 11, 12, 13, 6, 7, 8, 9. The main purpose of this sorting is to put the first three columns behind, the order of the three columns does not matter. For tables with less than 10 columns, there is no problem: SQL> create table t (c1 number, c2 number, c3 number, c4 number, c5 number); the table has been created. Www.2cto.com SQL> col column_name format a20SQL> select column_name, column_id 2 from user_tab_columns 3 where table_name = 'T' 4 order by decode (column_id, 1, null, 2, null, 3, null, column_id); COLUMN_NAME COLUMN_ID ------------------ ---------- C4 4C5 5C2 2C1 1C3 3 but it will be messy when the number of columns in the table is greater than 10. Www.2cto.com SQL> select column_name, column_id, decode (column_id, 1, null, 2, null, 3, null, column_id) sortcolumn 2 from user_tab_columns 3 where table_name = 't'4 order by decode (column_id, 1, null, 2, null, 3, null, column_id) 5/COLUMN_NAME COLUMN_ID SORTCOLUMN -------------------- ------------------------ C10 10 10C11 11 11C12 12 12C4 4 4C5 5 5C6 6 6C7 7 7C8 8 8C9 9 9C3 3C2 2C1 1 has selected 12 rows. Obviously, ORACLE sorts the SORTCOLUMN as a character type. Add TO_NUMBER to solve this problem. Www.2cto.com SQL> select column_name, column_id, decode (column_id, 1, null, 2, null, 3, null, column_id) sortcolumn 2 from user_tab_columns 3 where table_name = 't'4 order by to_number (decode (column_id, 1, null, 2, null, 3, null, column_id )); COLUMN_NAME COLUMN_ID SORTCOLUMN -------------------- ------------------------ C4 4 4C5 5 5C6 6 6C7 7 7C8 8 8C9 9 9C10 10 10C11 11C12 12 12C1 1C3 3C2 2 12 rows have been selected. But why does this problem occur? Why does the DECODE function return the character type. Master yangtingkun has written several articles on this issue. If you are interested, you can find it. Here, I would like to explain a little bit: For the NULL type, the default return type of ORACLE is VARCHAR. The type returned by the DECODE function ORACLE depends on the first value. For example: www.2cto.com SQL> CREATE TABLE A AS SELECT DECODE (DUMMY, 'x', 1, 'y', '2', DUMMY) C1, <--- because the first returned value 1 is of the integer type, the entire expression returns the integer type 2 DECODE (DUMMY, 'x', '1', 'y', 2, DUMMY) c2, <--- because the first returned value '1' is of the character type, the entire expression returns the CHAR type 3 DECODE (DUMMY, 'x', NULL, 'y ', 'huateng', DUMMY) C3 from dual; <--- because the first returned value is NULL, the entire expression returns the CHAR type table that has been created. SQL> is the name of DESC A empty? Type ----------------------------------------- -------- ---------------------------- C1 NUMBER C2 VARCHAR2 (1) C3 VARCHAR2 (7) is precisely because the DECODE function depends on the first value type as the return type, if other returned values do not match the first type, you may encounter A problem: SQL> DESC A; is the name blank? Type ----------------------------------------- -------- ---------------------------- C1 NUMBER C2 VARCHAR2 (1) C3 VARCHAR2 (7) SQL> INSERT INTO A VALUES (2, 2); 1 row has been created. SQL> SELECT * from a; C1 C2 C3 ---------- -- ------------ 1 1 2 2 2 SQL> SELECT DECODE (C1, 2, 'E', C1) FROM A; ERROR: ORA-01722: Invalid Number unselected row www.2cto.com SQL> SELECT DECODE (C1, 2, 'E', C1) FROM A WHERE C1 = 1; DECODE (C1, 2, 'E', C1) ----------------------- 1 SQL> SELECT DECODE (C1, 1, 1, 2, 'E', C1) FROM A WHERE C1 = 2; SELECT DECODE (C1, 1, 1, 1, 2, 'E', C1) from a where C1 = 2 * 1st row error: ORA-01722: Invalid Number above problem mainly character 'E' none Is converted to the integer type.