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:

Where:

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:

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

This should return true as shown in the following:

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

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:

This should return as follows:

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

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.

Result:

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

Example Result:

Value must be neither 1 or 2 |

Conclusion

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