DECODE Function

The DECODE expression is similar to the CASE expression in that it can print different results depending on the values found in a specified column.

Element	Description	Restrictions	Syntax
expr, else_expr, then_expr, when_expr	Expressions whose values and data types can be evaluated	Data types of when_expr and expr must be compatible, as must then_expr and else_expr. Value of when_expr cannot be a NULL.	Expression, p. Expression

The expressions expr, when_expr, and then_expr are required. DECODE evaluates expr and compares it to when_expr. If the value of when_expr matches the value of expr, then DECODE returns then_expr.

The expressions when_expr and then_expr are an expression pair, and you can specify any number of expression pairs in the DECODE function. In all cases, DECODE compares the first member of the pair against expr and returns the second member of the pair if the first member matches expr.

If no expression matches expr, DECODE returns else_expr. If no expression matches expr and you specified no else_expr, then DECODE returns NULL.

Suppose that a user wants to convert descriptive values in the evaluation column of the students table to numeric values in the output. The following table shows the contents of the students table.

firstname	evaluation		firstname	evaluation
Edward	Great		Mary	Good
Joe	Not done		Jim	Poor

The user now enters a query with the DECODE function to convert the descriptive values in the evaluation column to numeric equivalents:

Constant Expressions

firstname	evaluation		firstname	evaluation
Edward	100		Mary	50
Joe	-1		Jim	0

Certain expressions that return a fixed value are called constant expressions. Among these are the following operators (or system constants) whose returned values are determined at runtime:

CURRENT returns the current time and date, from the system clock.
CURRENT_ROLE returns the name of the role, if any, whose privileges are enabled for the current user.
DEFAULT_ROLE returns the name of the role, if any, that is the default role for the current user.
DBSERVERNAME returns the name of the current database server.
SITENAME is a synonym for DBSERVERNAME.
TODAY returns the current calendar date, from the system clock.
USER returns the login name (also called the authorization identifier) of the current user.

Besides these operators, the term constant expression can also refer to a quoted string, to a literal value, or to the UNITS operator with its operands.

The Constant Expression segment has the following syntax.

Constant Expressions:
 
                       (1)
|--+-| Quoted String |------------------------------------------------+--|
   |                    (2)                                           |
   +-| Literal Number |-----------------------------------------------+
   +-+-USER---------------------------+-------------------------------+
   | |  (3)    (4)                    |                               |
   | '---------------+-CURRENT_ROLE-+-'                               |
   |                 '-DEFAULT_ROLE-'                                 |
   |  (4)                                                             |
   '------+-+-SITENAME-----+----------------------------------------+-'
          | '-DBSERVERNAME-'                                        |
          +-TODAY---------------------------------------------------+
          +-CURRENT--+------------+---------------------------------+
          |          '- precision-'                                 |
          |                      (5)                                |
          +-| Literal DATETIME |------------------------------------+
          |                      (6)                                |
          +-| Literal INTERVAL |------------------------------------+
          +-num--UNITS--time_unit-----------------------------------+
          |  (3)                                                    |
          '------+-+------------+--+- sequence-+-- .--+-CURRVAL-+-+-'
                 | '- owner-- .-'  '- synonym--'      '-NEXTVAL-' |
                 |                        (7)                     |
                 +-| Literal Collection |-------------------------+
                 |                 (8)                            |
                 +-| Literal Row |--------------------------------+
                 +-literal opaque type----------------------------+
                 '-literal BOOLEAN--------------------------------'

Notes:

See page Quoted String
See page Literal Number
Dynamic Server only
Informix extension
See page Literal DATETIME
See page Literal INTERVAL
See page Literal Collection
See page Literal Row

Element	Description	Restrictions	Syntax
literal Boolean	Literal representation of a BOOLEAN value	Must be either t (TRUE) or f (FALSE)	Quoted string, p. Quoted String
literal opaque type	Literal representation of value of an opaque data type	Must be recognized by the input support function of opaque type	Defined by UDT developer
num	How many of specified time units. See UNITS Operator.	If num is not an integer, the fractional part is truncated	Literal Number, p. Literal Number
owner	Name of the owner of sequence	Must own sequence	Owner, p. Owner Name
precision	Precision of the returned DATETIME expression	None, but see Syntax reference in next column for valid syntax	DATETIME Qualifier, p. DATETIME Field Qualifier
sequence	Name of a sequence	Must exist in current database	Identifier, p. Identifier
synonym	Synonym for the name of a sequence	Must exist in current database	Identifier, p. Identifier
time_unit	Keyword to specify time unit: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, or FRACTION	Must be one of the keywords at left. Case insensitive but cannot be enclosed within quotes	See the Restrictions column.