Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Enterprise
Expertise: Intermediate
Sep 12, 2000

Use Decode Function for Oracle Queries

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;


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
    Result = OTHER
End Case


If Viewable = 1 Then
    Result = VISIBLE
Elsif Viewable = 2 Then
    Result = INVISIBLE
Elsif Viewable = 3 Then
    Result = UNKNOWN
    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.
Trupti Rajparia
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date