Top 5 usages of Decode in SQL

239 Oracle EBS

Decode in SQL is an Oracle Function that can be used as an if-then-else statement in SQL. This function is named DECODE.

This function allows us to add an if-then-else procedural logic to our SQL statements. DECODE statement is a very powerful function and many complex logics can be handled easily with this. Let’s get a complete understanding of this function and how it can be used.

But if you are in hurry to get the syntax it is

Select Decode( X, Y ,true, false) from dual;

Table of Contents

What is the DECODE function in SQL ?

As I mentioned in the introduction, In Oracle, SQL DECODE is an inbuilt function that can be used for comparisons. As in if the condition of equality matches it returns some value else it returns a different value.

Look at the below example

SELECT
DECODE(X, 1, 'Equal to one')
FROM
dual;

In the above example if X =1 the value returned will be “Equal to One”. Note that this does not have an else condition so if X != 1 it will return NULL.

Also X can be of any data type (number, varchar etc).

Now let’s see an example of DECODE function with Else condition


SELECT
DECODE(X, 1, 'Equal to one','Not Equal to One')
FROM
dual;

Now the above SQL statement will not only check the value of X =1 but also if X != 1 then It will return ‘Not Equal to One’ from the Else Condition

Using Decode to handle multiple Conditions

If for example below is the Pseudocode of a if then else statement with multiple conditions , how this will be handled by DECODE.

Let’s suppose the stock data is stored in a table STOCKS

STOCK	STOCK_NAME
ORCL	ORACLE
HLL	UNILEVER
RIL	RELIANCE INDUSTRIES

STOCKS TABLE

IF stock = 'ORCL' THEN
     stock_name := 'ORACLE';
ELSIF stock = 'HLL' THEN
     stock_name := 'UNILEVER';
ELSIF stock = 'RIL' THEN
   stock_name := 'RELIANCE INDUSTRIES';
ELSE  stock_name := 'Not Defined';
END IF;

We will write the DECODE Statement as follows.

SELECT DECODE ( STOCK , 'ORCL', 'ORACLE'
                     , 'HLL', 'UNILEVER'
                      , 'RIL', 'RELIANCE INDUSTRIES'
                      , 'Not Defined')
From STOCKS;

The above Select Statement will check each row for the value of STOCK and return the STOCK name as per the table which stores the conditional relation between stock and stock name.

Using Decode to check less than Greater than

In Oracle SQL DECODE function can also be used to check Greater than and less than. Let see an example where we use the SIGN function with DECODE.

To check 3 – 5 = less , 7-5 = greater , 5 and 5 as Equal

select decode (sign(5-5),0,’equal’,-1,’less’,1,’greater’) Equality_Check from dual

Decode in SQL — Equality check with DECODE

image 2 Decode in SQL 1 — Greater than check with DECODE

image 3 Decode in SQL 2 — Less than check with DECODE

Similarly, we can use decode to compare two dates (date1 and date2), where,

if date1 > date2, it should return date2. Else, the DECODE function should return date1

Select DECODE((date1 - date2) - ABS(date1 - date2), 0, date2, date1) from dual

Hope you got a clear idea of how the DECODE function works in Oracle SQL. SQL is getting better with every release with different analytical function . IF you are looking for SQL training you can check out below resources

Become an Oracle SQL Specialist

[content-egg module=Udemy]