The decode function can be used in SQL for and IF-THEN-ELSE construction. It's an alternative for the CASE statement which was introduced in Oracle 8.
Decode is a very useful and handy function for Oracle queries. It replaces the complex If-Then-Else logic, which is used to display different things based on different values in a column. This is usually used for pivoting purposes.
Syntax is like:
DECODE(expr, search, result
[, search, result ]...
[, default ]
)
DECODE compares expr to each search value one by one. If expr 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, then Oracle returns null.
The arguments can be any of the numeric types (NUMBER, BINARY_FLOAT, or BINARY_DOUBLE) or character types.
The search, result, and default values can be derived from expressions. Oracle Database uses short-circuit evaluation. That is, the database evaluates each search value only before comparing it to expr, rather than evaluating all search values before comparing any of them with expr. Consequently, Oracle never evaluates a search if a previous search is equal to expr.
Oracle automatically converts expr and each search value to the datatype of the first search value before comparing. Oracle automatically converts the return value to the same datatype as the first result. If the first result has the datatype CHAR or if the first result is null, then Oracle converts the return value to the datatype VARCHAR2.
In a DECODE function, Oracle considers two nulls to be equivalent. If expr is null, then Oracle returns the result of the first search that is also null.
The maximum number of components in the DECODE function, including expr, searches, results, and default, is 255.
An Example:
You could use the decode function in an SQL statement as follows:
SELECT supplier_name,
decode(supplier_id, 10000, 'IBM',
10001, 'Microsoft',
10002, 'Hewlett Packard',
'Gateway') result
FROM suppliers;
The above decode statement is equivalent to the following IF-THEN-ELSE statement:
IF supplier_id = 10000 THEN
result := 'IBM';
ELSIF supplier_id = 10001 THEN
result := 'Microsoft';
ELSIF supplier_id = 10002 THEN
result := 'Hewlett Packard';
ELSE
result := 'Gateway';
END IF;
The decode function will compare each supplier_id value, one by one.
More Examples:
Decode Demo #1
Decode Demo #2
Decode Demo #3
Decode Demo #4
DECODE vs. CASE
CASE can evaluate any expression, but DECODE is limited to compare discrete values. But CASE is more compex, has more code and less readable I think, so if you can do your job with simple DECODE, do it with DECODE.
Before version 8.1, the DECODE was the only thing providing IF-THEN-ELSE functionality in Oracle SQL. Because DECODE can only compare discrete values (not ranges), continuous data had to be contorted into discreet values using functions like FLOOR and SIGN. In version 8.1, Oracle introduced the searched CASE statement, which allowed the use of operators like > and BETWEEN (eliminating most of the contortions) and allowing different values to be compared in different branches of the statement (eliminating most nesting). In version 9.0, Oracle introduced the simple CASE statement, that reduces some of the verbosity of the CASE statement, but reduces its power to that of DECODE.
Another difference is, CASE is an ANSI standard, where as Decode is proprietary for Oracle.
Then comes 11g!
Traditional pivoting is done with DECODE and CASE in 10g or earlier. In 11g, there are two new operators: PIVOT and UNPIVOT. They can be used for pivoting. For more information refer to Reference links below.
|
Set as Homepage
Add to Favourites
Recommend
Contact
