What is the difference between DECODE and CASE? Please give some examples.
CASE is more elegant.
|DECODE||Simple CASE||Searched CASE|
SELECT DECODE ( size_code, 1, 'Small', 2, 'Medium', 3, 'Large', '?' ) AS size FROM orders;
SELECT CASE size_code WHEN 1 THEN 'Small' WHEN 2 THEN 'Medium' WHEN 3 THEN 'Large' ELSE '?' END AS size FROM orders;
SELECT CASE WHEN size_code = 1 THEN 'Small' WHEN size_code = 2 THEN 'Medium' WHEN size_code = 3 THEN 'Large' ELSE '?' END AS size FROM orders;
|Available in Oracle 5 (or earlier)||Available in Oracle 9.0||Available in Oracle 8.1|
|Compares 1st argument with 2nd, 4th, ... argument and, if equal, returns 3rd, 5th, ... argument||Compares 1st value with successive WHEN-values and, if equal, returns corresponding THEN-value||Evaluates successive WHEN-conditions and, if true, returns corresponding THEN-value|
The example above illustrates the type of job DECODE was designed to do: translating discrete values from a single column in one coding scheme (1, 2, 3) to de-coded values ('Small', 'Medium', 'Large'). The example above also illustrates the syntax used in the two different forms of the CASE statement: the simple CASE (closer to DECODE) and searched CASE. All three versions produce the same results. The example above does not illustrate the enormous potential of the CASE statement.
Before version 8.1, the DECODE was the only thing providing IF-THEN-ELSE functionality in Oracle SQL. Because DECODE can only compare discrete values (not ranges), continuous data had to be contorted into discreet values using functions like FLOOR and SIGN. In version 8.1, Oracle introduced the searched CASE statement, which allowed the use of operators like > and BETWEEN (eliminating most of the contortions) and allowing different values to be compared in different branches of the statement (eliminating most nesting). In version 9.0, Oracle introduced the simple CASE statement, that reduces some of the verbosity of the CASE statement, but reduces its power to that of DECODE.
Continue to the next example...