Using the NVL and the NVL2 Functions
The NVL function is used to replace a null value with another value. It can be used with numeric and non-numeric data type values. It is an extremely important function if the AVG group function is used in the query. The AVG function will calculate incorrect averages if the target set of values contains nulls because null values do not equal 0 and will not be included in the divisor as a 0 value would. The divisor of a set of values that has values of 0 will be larger than a comparable set that contains nulls instead of 0s.
The NVL function can be used to overcome this problem. This function can replace null values with a value of 0 (or any other value), which will cause the divisor to increase and correct ...
Get Oracle SQL and PL/SQL Handbook: A Guide for Data Administrators, Developers, and Business Analysts now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
