Q
Problem solve Get help with specific problems with your technologies, process and projects.

What does the decode function do, and how is it structured?

What does the decode function do, and how is it structured?


DECODE is an Oracle function that is best understood by an example

DECODE(mth,1,'Jan', 2,'Feb', 3,'Mar'
         , 4,'Apr', 5,'May', 6,'Jun'
         , 7,'Jul', 8,'Aug', 9,'Sep'
        , 10,'Oct',11,'Nov',12,'Dec'
           , 'Err')
    as mthname

Whatever value is in the "mth" column is searched in the list of values and if found, is decoded to the expression provided, and if not found, the default value is the result. In the example above, the month number is translated into a month name, and if for some reason the "mth" column has a value that is not in the list, the default 'Err' month name is supplied.

DECODE does not have to be used only in one-for-one translations. It is also useful for aggregations.

DECODE(mth,1,'Winter',2,'Winter',3,'Winter'
          ,4,'Spring',5,'Spring',6,'Spring'
          ,7,'Summer',8,'Summer',9,'Summer'
         ,10,'Fall' ,11,'Fall' ,12,'Fall'
            ,'Error')
    as season

Especially when incorporated into a database View, DECODE is valuable in avoiding unnecessary coding in applications that require the decoded expressions. The equivalent in standard SQL would be the CASE structure, which is more powerful as it allows range tests.

For More Information


This was last published in April 2001

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close