Decoder

DECODE Function

DECODE is a very useful feature in Oracle applications. And it has the functionality of a logical IF-THEN-ELSE. It is used to display different things based on different values in a column.

Here are the formats of the DECODE:

DECODE (value, search1, result1 [, search2, result2,] [, search3, result3,] ... .... [,] Default)

OR

DECODE (value, IF1, then1 [, IF2, then2,] [, IF3 then3,] ... ... .. [the other])

OR

DECODE (value, find1, replace1 [, find2, replace2,] [find3, replace3,] ... ... [,] by default)

In the above formats:

value represents the static value is either an expression or column in a table / view

search1, search2, search3 ... ... Or IF1, IF2, IF3, ... ... Or find1, find2, find3 ... ....

is the value that is compared to the value in the first argument of DECODE.

result1, result2, result3, .... or then1, then2, then3, .... or replace1, replace2, replace3 ... ... ..

is the return value, if the value is equal to search1, search2, search3 ... .. Or IF1, IF2, IF3, ... ... Or find1, find2, find3 ... ... .... Respectively.

default, or is optional. If no match is found, the decoder will return the default value. If default is omitted, then the function DECODE returns NULL.

Thus, DECODE compares the value to each search value by one. If the value is equal to a search, then Oracle Database returns the corresponding result. If no match is found, then Oracle returns default. If default is omitted, Oracle returns NULL.

Here are examples:

Example 1:

This example decodes the value DEPTNO. If DEPTNO is 10, while sales of the function returns, if DEPTNO is 20, then it returns "production" and so on. If DEPTNO is not 10, 20 or 30, then the function returns NULL.

SELECT ename, sal,

DECODE (DEPTNO, 10, "Sales"

20, "production"

30, "Search") that the department FROM emp;

Example 2:

This example decodes the value DEPTNO. If DEPTNO is 10, while sales of the function returns, if DEPTNO is 20, then it returns "production" and so on. If DEPTNO is not 10, 20 or 30, then the function returns "accounting".

SELECT ename, sal,

DECODE (DEPTNO, 10, "Sales"

20, "production"

30, "research", "Accounts") that the department from emp;

Example 3:

This example decodes the value DEPTNO. If DEPTNO is 10, while sales of the function returns, if DEPTNO is 20, then it returns "production" and so on. If DEPTNO is not 10, 20 or 30, then the value DEPTNO the function returns.

SELECT ename, sal,

DECODE (DEPTNO, 10, "Sales"

20, "production"

30, "Search", DEPTNO)

FROM emp;

Example 4:

This example returns the larger of the two given numbers. The sign (& & a.