The DECODE function in Oracle SQL / PLSQL has the functionality of IF-THEN-ELSE statement.
The Syntax for the DECODE function in Oracle SQL / PLSQL is:
SELECT DECODE(expression,search,result,[search, result]…[default])
- ‘expression’ is the value to be compared
- ‘search’ is the value to be compared with expression
- ‘result’ is the value which is returned if the expression matches search
- ‘default’ is option and is returned if search in not equal to expression. If default is omitted then DECODE will return NULL.
Let’s take an example for understanding:
Suppose we have a table named ‘employee’ as shown below:
Using DECODE in SELECT Statement
Suppose we write our query as:
SELECT employee_id ,DECODE(employee_id,101,'A' ,102,'B' ,103,'C' ,104,'D' ,'F') Decoded FROM employee;
We will get the following output:
The above query is similar to writing
IF employee_id =101 THEN Result = A ELSE IF employee_id = 102 THEN Result = B ELSE IF employee_id = 104 THEN Result = C ELSE IF employee_id = 104 THEN Result = D ELSE Result = F
Here we can see that we have used DECODE function as an IN THEN ELSE loop.