DECODE function usage in Oracle

DECODE function: Compares the input value with the parameter list in the function and returns a corresponding value based on the input value. The parameter list of a function is a sequence of several numbers and their corresponding result values. Of course, if it fails to match any real argument sequence, the function also has the default return value. Unlike other SQL functions, the DECODE function can recognize and operate null values. Syntax: DECODE (control_value, value1, result1 [, value2, result2…] [, Default_result]); control _ value to be processed. The DECODE function compares the value with a series of even orders to determine the return value. Value1 is a numerical value that forms an ordinal pair. If the input value matches the value, the corresponding result is returned. Corresponding to an empty return value. You can use the keyword NULL to indicate that result1 is the result value of an ordinal pair. If default_result does not match any value, the default value returned by the function. Example: select decode (x, 1, 'x is 1', 2, 'x is 2', 'others ') from dual when x is equal to 1, returns 'x is 1 '. If x is equal to 2, 'x is 2' is returned '. Otherwise, others is returned '. When we need to compare two values, we can use them together with the SIGN () function. Select decode (SIGN (5-6), 1 'Is Positive',-1, 'Is Nagative', 'Is Zero'), and can also be implemented using CASE: select case sign (5-6) WHEN 1 then' Is Positive 'when-1 then' Is Nagative 'else' Is Zero 'end from dual In addition, you can also use Decode in Order. For example, the table_subject table has the subject_name column. Sort by language, number, and external order. In this case, you can easily use Decode to complete the requirements. Select * from table_subject order by decode (subject_name, '', 1, 'mat', 2, '', 3) Author taoxingtianxia