SQL Statement---NVL usage

NVL function is a null-value conversion function

one, NVL (expression 1, expression 2)

If expression 1 is a null value, NVL returns the value of expression 2, otherwise returns the value of expression 1. The purpose of this function is to convert a null value (NULL) into an actual value. The value of its expression can be numeric, character, and date. But the data type of expression 1 and expression 2 must be the same type.

Pair of numeric types: NVL (comm,0);

to Character NVL (To_char (comm), ' No Commission ')

to date type NVL (HireDate, ' 31-dec-99 ')

Example:

Select    ENAME,NVL (To_char (comm), ename| | ' is not a salesperson! ') As COMMISSION  from emp


II, nvl2 (expression 1, expression 2, expression 3)

If expression 1 is empty, the return value is the value of expression 3. If expression 1 is not empty, the return value is the value of expression 2.

For example NVL2 (comm, ' Sal+comm ', sal)

NVL2 function test Comm

Returns the value of Sal if Comm is empty. If Comm is not empty (null), the value of the expression Sal+comm is returned.

SQL statement---NVL usage