Oracle decode () function

Tag:blog   io   ar    use     data    on    div   log   ad   

Meaning explanation: Decode (condition, value 1, return value 1, value 2, return value 2,... Value N, return 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 ifdecode (field or Field operation, value 1, value 2, value 3) The result of this function is that when the value of the field or Field operation is equal to the value 1 o'clock, the function returns A value of 2, otherwise the return value 3 of course value 1, the value 2, the value 3 can also be an expression, this function makes some SQL statement simple many use method: 1, compare size Select decode (sign (variable 1-variable 2), 1, variable 1, variable 2) from dual; --Take the lower value sign () function returns 0, 1, 1 For example: variable 1=10, variable 2=20 (variable 1-variable 2) returns the -1,decode decoding result as "Variable 1" for the purpose of taking a smaller value, depending on whether a value is 0, positive, or negative. 2, this function is used in the SQL statement, the function is described as follows: The Decode function is similar to a series of nested if-then-else statements. Base_exp and Compare1,compare2 and so on are compared in turn. If the base_exp and the compare are matched, the corresponding value of I is returned. If the base_exp does not match any of the compare values, the default is returned. Each compare value is evaluated sequentially, and if a match is found, the remaining compare values (if any) are no longer evaluated. A null base_exp is considered equivalent to the null compare value. If required, each compare value is converted to the same data type sing Woo the first compare value, which is also the type of the return value. The decode function is very useful in the actual development of combining the Lpad function, how to make the value of the primary key automatically add 1 and the front 0select Lpad (Decode (count (record number), 0,1,max (To_number (record number) +1)), 14, ' 0 ') Record number from Tetdmiseg:select decode (dir,1,0,1) the value from A1_intervaldir is 1 to 0 and 0 to 1 for example, what is the number of boys and girls I want to inquire about? Usually we write: SelecT Count (*) from table where gender = male; select count (*) from table where sex = female; Want to show together also to union, too troublesome with decode, only need a sentence select decode (sex, male, 1,0), decode (gender, female, 1,0) from table 3,order by a specific sort of character column you can also use decode in order by. Example: Table Table_subject, with subject_name columns. It is required to sort by: language, number, and order. At this point, it is very easy to use the decode to complete the requirements. SELECT * from Table_subject ORDER by decode (subject_name, ' language ', 1, ' Math ', 2,, ' Foreign Language ', 3)

　　

Oracle decode () function