| by Arround The Web | No comments

Oracle Decode

Oracle Database is one of the most influential SQL Databases. It is used to power the small and enterprise applications with sufficient security and performance.

This post aims to show you how to use the decode() function in Oracle Queries. The decode() function enables you to introduce the embedded if-then-else logic in an Oracle query.

Function Syntax, Parameters, and Return Value

The following code snippet denotes the function of the decode() function in Oracle:

DECODE( expression , search , result [, search , result]... [, default] );


  1. The expression parameter refers to the value that is compared to the search values.
  2. The search and result parameters create a pair where the result is returned if the expression matches the search value.
  3. On the other hand, the default parameter is an optional argument that determines the value to be returned if no match is located.

Example Function Usage

The following example shows a basic function usage on the decode() function:

SELECT decode(10, 10, 'True') FROM dual;

The function performs a conditional check as shown in the following:

If 10 = 10 then

Return “true”;

End if;

This should return true as shown in the following:



True |

If the condition is false, the function returns null as shown in the following:

SELECT decode(10, 11, 'True') FROM dual;

Since the condition is false, the function returns NULL in this case.

We can include a return value if the condition is false as shown in the following:

SELECT decode(10, 11, 'True', 'False') FROM dual;

This should return as follows:



False |

We can also compare the first argument with a list of arguments as shown in the following:

SELECT decode(1, 2, 'One', 1, 'One') FROM dual;

In this case, the function compares the first argument, with the value of 1, with the second argument. If it is equals, the function returns the third argument. Otherwise, it compares the value with the fourth argument and returns the fifth argument as true.




One |

We can also define a return value if no match is found.

SELECT decode(5, 2, 'One', 1, 'One', 'Value must be neither 1 or 2') FROM dual;

Example Result:



Value must be neither 1 or 2 |


This post covered the basics of working with Oracle’s decode() function to handle more complex if-else statements in Oracle tables.

Share Button

Source: linuxhint.com

Leave a Reply