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

• What do you think about this answer? E-mail us at [email protected] with your feedback.
• The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
• The Best SQL Web Links
• Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
• Ask the Experts yourself: Our SQL guru is waiting to answer your technical questions.