· Meaning Explanation: Decode (condition, value 1, translation value 1, value 2, translation value 2, ... value n, translation value n, default) 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) End IF
· How to use: 1. Compare a large SELECT DECODE (SIGN (Variable 1-Variable 2), - 1, Variable 1, Variable 2) from Dual; - Take a smaller value SIGN () function According to a certain value is 0, The number or negative, return 0, 1, -1, for example: variable 1 = 10, variable 2 = 20, SIGN (Variable 1-Variable 2) Returns -1, decode decoding result is "variable 1", reaching smaller The purpose of the value.
There are student transcripts, now you have to use the decode function to achieve the following features: grades> 85, show excellent;> 70 shows good;> 60 and gauge; otherwise it is not grid.
Suppose the number of Student's numbers is ID, the score is score, then:
SELECT ID, DECODE (Sign (Score-85), 1, 'Excellent', 0, 'Excellent', -1, Decode (Sign (Score-70), 1, 'Good', 0, 'Good', -1 , decode (SIGN (Score-60), 1, 'and Grid', 0, 'and Grid', -1, 'fail'))) from student;
