SQL Decode

Decode will act as value by value substitution.

    For every value of field, it will checks for a match in a series of if/then tests.

    Syntax: decode (value, if1, then1, if2, then2, ……. else);

    Ex:

          SQL> select sal, decode(sal,500,'Low',5000,'High','Medium') from emp;

                                                 SAL     DECODE

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

       500           Low

      2500         Medium

      2000         Medium

      3500         Medium

      3000         Medium

      5000         High

      4000         Medium

      5000         High

      1800         Medium

      1200         Medium

      2000         Medium

      2700         Medium

      2200         Medium

      3200         Medium

SQL> select decode(1,1,3), decode(1,2,3,4,4,6) from dual;

DECODE(1,1,3) DECODE(1,2,3,4,4,6)

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

               3                      6

Ø If the number of parameters are odd and different then decode will display nothing.

Ø If the number of parameters are even and different then decode will display last value.

Ø If all the parameters are null then decode will display nothing.

Ø  If all the parameters are zeros then decode will display zero.