Oracle converts null values to other values __oracle

--Start

I personally think that there should not be a null value in the database because he subverted the two-valued logical structure (i.e. true and false), and a three-valued logical structure (i.e. true, false, and unknown) occurred. Because of NULL, our SQL statements are likely to have unexpected results. In addition, null values and other values for numeric operations can also cause problems. However, sometimes some things are not we can control and change, as a real programmer, should dare to face the most garbage database design. Here's how to convert a null value to another value.

CREATE TABLE EMPLOYEE
(
    name      VARCHAR2) not NULL   --Name
    SALARY number    ,                  base salary
    BONUS     number                   -bonus
);

INSERT into EMPLOYEE VALUES (' John ', 3000.0, NULL);
INSERT into EMPLOYEE VALUES (' Dick ', 4000.0, 0.0);
INSERT into EMPLOYEE VALUES (' Harry ', 5000.0, 1000.0);

--The following statements are equivalent to SELECT case when
BONUS is NULL THEN 0.0 ELSE BONUS end from EMPLOYEE;
SELECT COALESCE (BONUS, 0.0) from EMPLOYEE;
SELECT NVL (BONUS, 0.0) from EMPLOYEE;


--The following two statements are equivalent to the SELECT case when
BONUS isn't NULL THEN BONUS ELSE 0.0 end from EMPLOYEE;
SELECT NVL2 (BONUS, BONUS, 0.0) from EMPLOYEE;

If you want to find a total salary (basic salary + bonus) is greater than 3000 yuan employees, we will naturally write the following statement:

SELECT * from EMPLOYEE WHERE SALARY + BONUS >= 3000.0;

Unfortunately, this statement is not always true, and when salary or bonus has a value that is null, we are likely to miss out on some of the data. Friends think about it for themselves, which is one of the reasons why I don't think there should be a null value in the database, and if you're not the decision-maker and can't change the database design, we can write this:

SELECT * from EMPLOYEE WHERE NVL (SALARY, 0.0) + NVL (BONUS, 0.0) >= 3000.0;

Is there a simpler way? The answer is yes.

-If the condition is false or unknown, the LNNVL function returns the true
SELECT * from EMPLOYEE WHERE LNNVL (SALARY + BONUS < 3000.0);

Knowing how to convert null to another value, what do you do now that you have to turn the other values into null? Oh, try the following statement.

SELECT Nullif (bonus,0.0) from EMPLOYEE;
SELECT case When BONUS = 0.0 THEN NULL ELSE BONUS end from EMPLOYEE;

There is also a function to convert Binary_float_nan and Binary_double_nan to other values.

SELECT NANVL (Binary_float_nan, 0.0) from DUAL;
SELECT NANVL (Binary_double_nan, 0.0) from DUAL;

-- more see: Oracle SQL Extract

-- statement: Reprint please indicate the source

--Last edited on 2015-06-19

--Created by Shangbo on 2014-12-18

--End