Using Decode and Case in SQL

The Decode Function is analogous to the "IF THEN ELSE" conditional Statement.

Syntax

Decode(Expression, Search, Result[,Search,result]...[,default])

SQL> select ename,

  2  decode(deptno, 10, 'operations',

  3  'others') result

4  from emp;

ENAME      RESULT

---------- ----------

SMITH      others

ALLEN      others

WARD       others

Evaluates a list of conditions and returns one of multiple possible result expressions.

Simple CASE expression: 

CASE input_expression 

     WHEN when_expression THEN result_expression [ ...n ] 

     [ ELSE else_result_expression ] 

END

SQL> select empno,ename,

  2  case deptno

  3  when 10 then 'reasearch'

  4  when 20 then 'operations'

  5  else 'other'

  6  end as dept_name

  7  from emp;

     EMPNO ENAME      DEPT_NAME

---------- ---------- ----------

      7369 SMITH      operations

      7499 ALLEN      other

      7521 WARD       other

      7566 JONES      operations

      7654 MARTIN     other