Main differences between Case and Decode statements are:
- Easier to Read: CASE is more flexible and easier to read than DECODE.
- ANSI Compatible: CASE is an ANSI standard. But DECODE is internal to Oracle.
- Location: DECODE is used only inside SQL statement. We can use CASE any where in SQL, even as a parameter of a function/procedure.
- Check: DECODE works on the basis of an equality check. CASE can do many types of logical comparisons like < > etc.
- Decision Making: We can not use complex decision making statements in a DECODE function. We cannot do decode( price = 100,’cheap’,10000,’expensive’,’ok’ )
- Different Types; DECODE can take different types of expressions. But CASE has only one type of expression.