DB GEEK

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:

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:

SELECT
location_id,
country_id,
DECODE(country_id,'IT','ITALY',
'JP','JAPAN',
'US','UNITED STATES',
'NO COUNTRY') AS country_name
FROM
locations where location_id<=1900;

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

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!