DECODE

Syntax

DECODE (expr, [search, result]..., default)

Description

To evaluate this expression, the OpenAccess SDK SQL engine compares expr to each search value one by one. If expr is equal to a search, the OpenAccess SDK SQL engine returns the corresponding result. If no match is found, the OpenAccess SDK SQL engine returns the default, or, if default is omitted, returns null. The return value is of the same data type as the first result expression. The search, result, and default values can be derived from expressions.

The OpenAccess SDK SQL engine evaluates each search value only before comparing it to expr, rather than evaluating all search values before comparing any of them with expr. Consequently, OpenAccess SDK SQL engine never evaluates a search if a previous search is equal to expr.

The OpenAccess SDK SQL engine automatically converts expr and each search value to the data type of the first search value before comparing. The OpenAccess SDK SQL engine automatically converts the return value to the same data type as the first result. If the first result has the data type CHAR or if the first result is null, then the OpenAccess SDK SQL engine converts the return value to the data type to CHAR.

In a DECODE expression, the OpenAccess SDK SQL engine considers two nulls to be equivalent.

If expr is null, the OpenAccess SDK SQL engine returns the result of the first search that is also null. The maximum number of components in the DECODE expression, including expr, searches, results, and default is 255.

Example

SELECT DECODE(empno, 1, 'E1', 2, 'E2', 'DEFAULT') FROM emp;

# First Result expression is NULL. Result should be type XO_TYPE_CHAR

SELECT DECODE(empno, 1, NULL, 2, 'E2', 'DEFAULT') FROM emp;

# Input expression is NULL, Result should match the result of NULL search expr

   SELECT DECODE(ename, 'Bob', 'My Bob', 'Mary', 'My Mary', NULL, 'New Name', 'Default Name') FROM emp;
  

# no default value, so return NULL for non-match values

SELECT DECODE(empno, 1, 'E1', 2, 'E2') FROM emp;

# ERROR CHECKING

# Invalid number of arguments

   # Invalid syntax used with scalar function:DECODE. Function expects 3 arguments.
  

SELECT DECODE() FROM emp;

SELECT DECODE(empno, 1) FROM emp;

# Conversion errors

# decode() Error converting value of result expression to XoType:<4>

SELECT DECODE(empno, 1, 10, 2, 20, 'abc') FROM emp;