|Lesson 8||DECODE and NVL functions|
|Objective||Modify query results using the DECODE and NVL functions.|
NVLfunctions are really special. They can be used for date, number, and character datatypes. I have found these two functions to be great time-savers.
DECODEas a mini-
IF-statement. It can compare the value in a column or expression to a list of values and substitute a different value for each item on the list. The basic syntax is:
DECODE(col1,case1,value1 [, case2, value2,...] , defaultvalue)
DECODEto create columns of summarized data in a single query.
NVLis a simple function that replaces a null value with the specified value. It is useful when you want to perform arithmetic on columns that might contain null values. In these cases, a null value will cause the arithmetic to result in a null value. To avoid this, you can substitute zero for null value using the
NVLfunction. Values that are anything other than null remain unchanged. The syntax is shown below.