Case Statement and Decode Function

Case Statements:
---------------------
-->CASE statement has the functionality of an IF-THEN-ELSE statement. Starting in Oracle 9i, you can use the CASE statement within a SQL statement or Pl/sql as well.
syntax:
CASE [ expression ]
   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n
   ELSE result
END
Expression is optional :- It is the value that you are comparing to the list of conditions.
Condition_1 to
Condition_n :- Must all be the same datatype. Conditions are evaluated in the order listed.
        Once a condition is found to be true, the CASE statement will return the result and not evaluate the conditions any further.
Result_1 to result_n :- must all be the same datatype. This is the value returned once a condition is found to be true.
-->If no condition is found to be true, then the CASE statement will return the value in the ELSE clause.
-->If the ELSE clause is omitted and no condition is found to be true, then the CASE statement will return NULL.
-->You can have up to 255 comparisons in a CASE statement. Each WHEN ... THEN clause is considered 2 comparisons.
You could use the CASE statement in a SQL statement as follows: (includes the expression clause)
SELECT table_name,
CASE owner
  WHEN 'SYS' THEN 'The owner is SYS'
  WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
  ELSE 'The owner is another value'
END
FROM all_tables;
Or you could write the SQL statement using the CASE statement like this: (omits the expression clause)
SELECT table_name,
CASE
  WHEN owner='SYS' THEN 'The owner is SYS'
  WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
  ELSE 'The owner is another value'
END
FROM all_tables;
The above two CASE statements are equivalent to the following IF-THEN-ELSE statement:
IF owner = 'SYS' THEN
   result := 'The owner is SYS';
ELSIF owner = 'SYSTEM' THEN
   result := 'The owner is SYSTEM'';
ELSE
   result := 'The owner is another value';
END IF;
-->Multiple expression in case using and operator:
SELECT supplier_id,
CASE
  WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN 'North office'
  WHEN supplier_name = 'IBM' and supplier_type = 'Software' THEN 'South office'
END
FROM suppliers;
So if supplier_name field is IBM and the supplier_type field is Hardware, then the CASE statement will return North office.
If the supplier_name field is IBM and the supplier_type is Software, the CASE statement will return South office.

Decode Function:
------------------------
-->DECODE function has the functionality of an IF-THEN-ELSE statement.
--> maximum number of components that you can have in a DECODE function is 255. This includes the expression, search, and result arguments.
Syntax:DECODE( expression , search , result [, search , result]... [, default] )
Parameters or Arguments
expression :- is the value to compare.
search :-  It is the value that is compared against expression.
result :-  It is the value returned, if expression is equal to search.
default :- It is optional. If no matches are found, the DECODE function will return default.
    If default is omitted, then the DECODE function will return null (if no matches are found).
SELECT supplier_name,
DECODE(supplier_id, 10000, 'IBM',
                    10001, 'Microsoft',
                    10002, 'Hewlett Packard',
                    'Gateway') result
FROM suppliers;
The above DECODE statement is equivalent to the following IF-THEN-ELSE statement:
 IF supplier_id = 10000 THEN
   result := 'IBM';
ELSIF supplier_id = 10001 THEN
   result := 'Microsoft';
ELSIF supplier_id = 10002 THEN
   result := 'Hewlett Packard';
ELSE
   result := 'Gateway';
END IF;