Top 5 usages of Decode in SQL

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;

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

STOCKSTOCK_NAME
ORCLORACLE
HLLUNILEVER
RILRELIANCE 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]

FAQ:

Is DECODE procedure or function?

DECODE is a Function that returns a value based on the condition.

Can DECODE be used inside the Anonymous block in PL SQL?

Yes.

Why is DECODE mostly used?

It is used to check conditional expressions inside PL SQL programs.

You May Like

wpsadmin
We will be happy to hear your thoughts

Leave a reply

Ads Blocker Image Powered by Code Help Pro

Ads Blocker Detected!!!

We have detected that you are using extensions to block ads. Please support us by disabling these ads blocker.

Powered By
Best Wordpress Adblock Detecting Plugin | CHP Adblock
Cloud Technologies Blog
Logo
Compare items
  • Total (0)
Compare
0
Shopping cart