Oracle/ PLSQL: Decode Function

Oracle/ PLSQL: Decode Function:

Decode has the functionality a IF - THEN - ELSE statement.

The syntax for decode function is :

   DECODE(expression, search, result [,search,result]...[,default])


Expression is the values to be compared.
Search is the value compared against expression.
Result is the value returned, if expression equal to search.
Default is an optional field. If no matches are found default would be returned. If default is omitted the decode would return a NULL value when there is no match.

The max number of arguments the Decode function can have is 255
(When more than 255 arguments are passed we always get a ORA-00939: too many arguments for function exxception).

An example:

SELECT DECODE(department_id, 100, 'Finance',
                                                        200, 'Audit',
                                                        300, 'IT',
                                                        400, 'Engineering',
                                                        500, 'Admin',
                                                                'Management') result
FROM departments;

The above DECODE function can be written using IF - THEN - ELSE

IF department_id = 100 THEN
result := 'Finanace';
ELSIF department_id = 200 THEN
result := 'Audit';
ELSIF department_id = 300 THEN
result := 'IT';
ELSIF department_id = 400 THEN
result := 'Engineering';
ELSIF department_id = 500 THEN
result := 'Admin';
ELSE
result := 'Management';
END IF;


It is always a good practice to keep the datatype of the result in the Decode function to be consistent.

SELECT DECODE(abc, 'TRUE', 'T',
                                          'FALSE', 'F',
                                          NULL, 1,
                                                      0) Result FROM DUAL;

In the above statement depending on the value of abc the statement would return 'T', 'F', 1, 0.
There is no syntax error here but its a bad practice to have more then one datatype in the return value.
There is a possibility of a run time error(ORA: 06512 invalid number exception or value error exception) while using inconsistent datatypes in the return clause

SELECT DECODE(abc, 'TRUE', 'T',
                                          'FALSE', 'F',
                                          NULL, '1',
                                                      '0') Result FROM DUAL;

Always a good practice to use similar datatypes as in the above statement 'T','F','1','0'.