Oracle db nvl, NVL2, NULLIF, COALESCE Functions

• Regular Functions

The following functions are applicable to any data type and use null values: • NVL (expr1, expr2) • NVL2 (expr1, expr2, expr3) • NULLIF (expr1, expr2) • COALESCE (expr1, expr2 ,..., exprn) the following functions are applicable to any data type and use null values in the expression list.

• NVL Function

Convert null values to actual values: • data types that can be used are date, character, and number. • Data Types must match:-NVL (commission_pct, 0)-NVL (hire_date, '01-JAN-97 ')-NVL (job_id, 'nojob yun ') to convert a null value to an actual value, use the NVL function. Syntax NVL (expr1, expr2) in this syntax: • expr1 is the source value or expression that may contain null values • expr2 is the target value used to convert null values. You can use the NVL function to convert any data type, however, the returned value always has the same data type as expr1. NVL conversion of various data types

• Use NVL Functions

Hr @ TEST0924> SELECT last_name, salary, NVL (commission_pct, 0), (salary * 12) + (salary * 12 * NVL (commission_pct, 0) AN_SAL FROM employees; LAST_NAME salary nvl (COMMISSION_PCT, 0) AN_SAL------------------------------------------------------------------OConnell 2600 0 31200Khoo 3100 0 37200Vargas 2500 0 30000Russell 14000. 4 235200Bernstein 9500. 25 142500To calculate the annual compensation for all employees, multiply the monthly salary by 12 and then add the Commission percentage:Hr @ TEST0924> SELECT last_name, salary, commission_pct, (salary * 12) + (salary * 12 * commission_pct) AN_SAL FROM employees; LAST_NAME SALARY COMMISSION_PCT AN_SAL-----------------------------------------------------------OConnell 2600Vargas 2500Russell 14000. 4 235200Taylor 3200Please note that the annual compensation is calculated only for the employees who receive the Commission. If any column value in the expression is null, the result is also empty. To calculate the annual compensation for all employees, the null value must be converted to a number before arithmetic operators can be applied. In this example, the NVL function is used to convert null values to zero values.

For more details, please continue to read the highlights on the next page:

Oracle COALESCE Functions

Common Oracle functions: nvl/NULLIF/case when/wm_concat/replace

Oracle NULLIF Function

Use of Oracle NULLIF Functions

Introduction to Oracle nvl () and NVL2 () Functions

NVL functions in Oracle

About NVL functions in Oracle databases

1 2 3 Next Page