Wiki: DECODE

DECODE #

Functionality:#

This Oracle function gives us the ability to have a function with an IF-THEN-ELSE type of construct.

The Oracle CASE WHEN Construct often provides a clearer explanation of the logic.

Parameters:#

EXPRESSION	The value you wish to evaluate
SEARCH1	The value that is compared to the Expression
RESULT1	What is returned if the Expression is equal to the value
SEARCH2	(optional) The value that is compared to the Expression
RESULT2	(optional) What is returned if the Expression is equal to the value

... et cetera ... as many search/result pairs as you need

DEFAULT	(optional) What is returned if the Expression is not equal to any of the values provided.

Returns:#

The same variable type as Expression

Errors:#

None

Operations: #

The function will evaluate the value passed in Expression, and process it through a series of Search-Result pairs. If the value passed is equal to any of the Search sets, then the corresponding Result is returned.

If the optional Default value is included, then this is what is returned if the expression is not equal to any of the Search values provided.

If you do not provide a Default value, and the expression is not equal to any of the search values provided, then NULL is returned. The type of data (date/number/character) of the first result must match the results of all the other values, otherwise you may get oracle errors.

Example:

DECODE(~,1000,'Alpha',2000,'Bravo','Charlie') will evaluate the variable passed and compare it to the numbers 1000 (return Alpha) or 2000 (return Bravo) and if neither of these values will return Charlie. In programming terms this would look like:

IF ~ = 1000 THEN
  return ‘Alpha’
ELSEIF ~ = 2000 THEN 
  return ‘Bravo’
ELSE 
  return ‘Charlie’

Without the last parameter (Charlie), the ELSE condition at the end would return NULL.

Notes #

Click to create a new notes page