The LNNVL function determines whether one or two operands in a condition are NULL. You can use this function in a WHERE clause or use the function as the WHEN condition in a CASE expression. A condition is used as a parameter. If the condition is FALSE or UNKNOWN, TRUE is returned. If the condition is TRUE, FALSE is returned.
Syntax
LNNVL(condition)
Parameters
| Parameter | Description |
|---|---|
| condition | The condition. |
Assume that a is equal to 2 and the value of b is NULL . The following table lists the return values of the LNNVL function.
| Condition | Check result of the condition | Return value of LNNVL |
|---|---|---|
| a = 1 | FALSE | TRUE |
| a = 2 | TRUE | FALSE |
| a IS NULL | FALSE | TRUE |
| b = 1 | UNKNOWN | TRUE |
| b IS NULL | TRUE | FALSE |
| a = b | UNKNOWN | TRUE |
Return type
TRUE or FALSE of the BOOLEAN type is returned.
Examples
Assume that data is inserted into the employee name column name and the commission column commission_pct in an EMPLOYEES table. Execute the following statements:
CREATE TABLE EMPLOYEES (name VARCHAR(20), commission_pct numeric);
INSERT INTO EMPLOYEEs VALUES ('Baer', null);
INSERT INTO EMPLOYEEs VALUES ('Bada', null);
INSERT INTO EMPLOYEEs VALUES ('Boll', 0.1);
INSERT INTO EMPLOYEEs VALUES ('Bates', 0.15);
INSERT INTO EMPLOYEEs VALUES ('Eros', null);
INSERT INTO EMPLOYEEs VALUES ('Girl', 0.25);
You want to know the number of employees whose commission rates are less than 20%, including the employees who do not receive commissions. You can query only the number of employees whose actual commission rates are less than 20% by executing the following statement:
SELECT COUNT(*) FROM employees WHERE commission_pct < .2;
The following query result is returned:
+------------+
| COUNT(*) |
+------------+
| 2 |
+------------+
To include another three employees who do not receive commissions, you must rewrite the query by using the INNVL function. Execute the following statement:
SELECT COUNT(*) FROM employees WHERE LNNVL(commission_pct >= .2);
The following query result is returned:
+------------+
| COUNT(*) |
+------------+
| 4 |
+------------+
