Oracle – DECODE Function

decode

admin Functions, Oracle , , , , , , , ,

Hi Geeks,
DECODE is very intersting function provided by Oracle. It is SQL version of IF…THEN…ELSE… block.
Let’s have a look at the systax which will help us to understand the working scenarios of this functions.

Syntax:

DECODE(expr, search, result, default)

expr
It can be a column name from a table or a string. If it is a char string, it must be enclosed in single quoration marks.

search
User provides an argument as a string or expression which needs to be searched in expr.

result
If expr and search are equal then Oracle returns corresponding results. It can be a column name or a string.

default
If expr is not equal to search then Oracle returns default. This is an optional argument. If it is omitted, then Oracle returns null.

The maximum number of arguments in the DECODE function, including expr, searches, results, and default, are 255.

You can also use CASE Expressions to achieve the same output.
Let’s understand the concept with an example.

Example:

When we run above SQL statement, Oracle will generate below output.

LOCATION_IDCOUNTRY_IDCOUNTRY_NAME
1000ITITALY
1100ITITALY
1200JPJAPAN
1300JPJAPAN
1400USUNITED STATES
1500USUNITED STATES
1600USUNITED STATES
1700USUNITED STATES
1800CANO COUNTRY
1900CANO COUNTRY

We can see in the above table that Country_Name column is showing Countries as per the Country_IDs which we evaluated in our SQL code.
Looking at the syntax and the SQL code might confuse you.

While using DECODE, you must remember a few points.

  • First argument is always treated as expr.
  • After that search and result arguments are inter-dependent. It means these arguments are found in bound relation. e.g.
    • 2nd and 3rd
    • 4th and 5th
    • 6th and 7th
    • and so on…
  • If any argument is found alone (without it’s pair), Oracle considers it as defaul.
  • First 3 arguments are compulsory to complete a case. After that every argument which has odd index number consider as default argument if it does not have it’s pair argument.
  • In our case, ‘NO COUNTRY’ is available has index number 8 and it does not have a pair argument so for all other records which are not in our test case, Oracle has set ‘NO COUNTRY’ in COUNTRY_NAME column.

If you have any questions/suggestions, please comment below or write me an email.

If you like this blog, do share with your friends and colleagues on your social media.
For more updates join my facebook group and do like my facebook page.
You can also learn some great SQL tricks at SQL Tricks and Workarounds.

Thank you,
Kapil Kumar

Sharing is caring!

You May Also Like..

NULLIF

Oracle SQL_Null Related functions-3_NULLIF

Hi Aliens, Today, we will talk about another Null Related Function, NULLIF. You can read my notes on NVL, NVL2 […]

WHERE

SQL – WHERE clause

Hi DBgeek Army, Welcome back to the world of Oracle! Where clause helps to select desired rows or helps to […]

comments

Comment in SQL and PL-SQL

Hi DBgeek Army, Welcome back to the world of Oracle! Comments are very important aspects of our code. No matter […]

Leave a Reply

Your email address will not be published. Required fields are marked *