Oracle Decode Function

This function has the functionality of if-then-else statement. For example consider you want to print the order report as orderno, customer and status columns and for the status column you need to print Draft/InProgress/Complete but in order table this field is number that is 1/2/3 respectively. So you need to achieve the following block in SQL statement

IF o.status ==1 THEN

   status := ‘Draft’;

ELSIF o.status ==2 THEN

   status := ‘In Progress’;

ELSE

   status := ‘Complete’;

 

Decode function helps easily to achieve this result without IF Then, the syntax for the decode function is:

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

“expression” is the value to compare, in our example it is o.status
“search” is compared against expressions, in our example it is 1 and 2
“result ” is the return value in our example it is ‘Draft’, ‘In Progress’
“default” is optional when no match found it will return, in our example it is ‘Complete’

The select query for our example is given below

Select o.orderno,o.customer, decode (o.status,1,’Draft’,2,’In Progress’,’Complete’) status from order o;

The decode function will compare each value, one by one and returns the matched expressions. If there is no match, the default value is returned (if defined), else it returns NULL.