Some other useful functions:
Example: greatest (2, 5, 11, 3) returns 11.
greatest (‘2’, ‘5’, ‘11’, ‘3’) returns ‘5’ (compares lexicographically for strings)
It is like a case statement.
// Example 1: (Converts column region from ‘N’ to North, ‘S’ to South and so on)
// Example 2: (book_type and pub_name are columns in below query)
We could have also achieved the above by a double group-by function as
group by pub_name, book_type
This would avoid the use of decode but then the resulting table would not have all the data related to pub_name in one row (it would be spread across multiple rows).
While this may be ok if the result of this query has to be processed in Java program but the same may not be good if the result of this query has to be feeded into another query.
A problem with above approach (as compared to double group by) is that it can work only if the book_type values are known beforehand and their count is not much. If that were not so, then it would be a pain to write all the known book-types into one query and SQL also might have some limitation on that (like CASE statement has).
Nevertheless, the above is a great example to see usefulness of decode for smaller range columns. Also, if we need to break up a column into ranges, then decode is very powerful, as shown in next example:
// Example 3: Below query selects SAL column into ranges.
// greatest(SAL,3001) will select SAL only if SAL > 3001
// least (SAL,9999) will select SAL only if SAL < 9999
// And if the two are equal, then we will get 1 else 0
// Hence column named “$3001-9999” in the output will return
// the number of salaries between 3001 and 9999
|Email:||(Your email is not shared with anybody)|