Difference between DECODE and CASE

Everything DECODE can do, CASE can. There is a lot more that you can do with CASE, though, which DECODE cannot. below are the list of differences:

• DECODE can work with only scalar values but CASE can work with logical operators, predicates and searchable sub queries.

• CASE can work as a PL/SQL construct but DECODE is used only in SQL statement.CASE can be used as parameter of a function/procedure.

• CASE expects datatype consistency, DECODE does not.

• CASE complies with ANSI SQL. DECODE is proprietary to Oracle.

• CASE executes faster in the optimizer than does DECODE.

• CASE is a statement while DECODE is a function.