Home » Tip Bank » Database Development » SQL » PL/SQL » SQL

Language: Enterprise
Expertise: Intermediate

Sep 12, 2000

Decode is a very useful and handy function for Oracle queries. It replaces the complex If-Then-Else logic, which is used to display different things based on different values in a column.

Consider the following example in which the Viewable column can have values of 1, 2, and 3:

 SELECT FirstName, Viewable
FROM   employee;

Results:

FIRSTNAME		Viewable
===========================
John			1
Tim			2
Julie			2
Stacy			1
Rahul			3
Leena			4
Amy			1
Bill			3
Teri			3

Now, we can use Decode to display different things in a report based on the values in Viewable.

 SELECT Firstname, 
     Decode(Viewable, 1,'VISIBLE', 2,'INVISIBLE', 
3,'UNKNOWN', 'OTHER')
FROM   employee;

Results:

FIRSTNAME		Viewable
===========================
John			VISIBLE
Tim			INVISIBLE
Julie			INVISIBLE
Stacy			VISIBLE
Rahul			UNKNOWN
Leena			OTHER
Amy			VISIBLE
Bill			UNKNOWN
Teri			UNKNOWN

Decode checks the column values and interprets the provided values in pairs. This is how it works:

 Switch viewable:

Case 1:
    Result = VISIBLE
Case 2:
    Result = INVISIBLE
Case 3:
    Result = UNKNOWN
Default:
    Result = OTHER
End Case

OR
====================

If Viewable = 1 Then
    Result = VISIBLE
Elsif Viewable = 2 Then
    Result = INVISIBLE
Elsif Viewable = 3 Then
    Result = UNKNOWN
Else
    Result = OTHER
End If

One can use Decode in Updates as well. For example, instead of writing 50 different updates to change state names, we can do it in one Update using Decode as follows:

 Update employee
set    homestate = decode(homestate, 
                        'AL', 'Alabama',
                        'AK', 'Alaska',
                        'AS', 'American Samoa',
                        'AZ', 'Arizona',
				. . . . . . . .
                        . . . . . . . . 
                        'WA', 'Washington',
                        'WV', 'West Virginia',
                        'WI', 'Wisconsin',
                        'WY', 'Wyoming' , homestate)
 where homecountry = 'UNITED STATES'

This will replace state abbreviations with state names. And it leaves the abbreviations alone in case it doesn't match any of the values, such as AL, AK, etc.