In Oracle/PLSQL, the LNNVL function is used in the WHERE clause of an SQL statement to evaluate a condition when one of the operands may contain a NULL value.
The syntax for the LNNVL function is:
LNNVL( condition )
The LNNVL function will return the following:
Condition Evaluates To | LNNVL Return Value |
TRUE | FALSE |
FALSE | TRUE |
UNKNOWN | TRUE |
So if we had two columns called qty and reorder_level where qty = 20 and reorder_level IS NULL, the LNNVL function would return the following:
Condition | Condition Evaluates To | LNNVL Return Value |
qty = reorder_level | UNKNOWN | TRUE |
qty IS NULL | FALSE | TRUE |
reorder_level IS NULL | TRUE | FALSE |
qty = 20 | TRUE | FALSE |
reorder_level = 20 | UNKNOWN | TRUE |
Applies To:
- Oracle 10g, Oracle 11g
For example:
Let's take a look at an example. If we had an products table that contained the following data:
PRODUCT_ID | QTY | REORDER_LEVEL |
1000 | 20 | NULL |
2000 | 15 | 8 |
3000 | 8 | 10 |
4000 | 12 | 6 |
5000 | 2 | 2 |
6000 | 4 | 5 |
And we wanted to find all of the products whose qty was below their respective reorder levels, we would run the following SQL statement:
select * from products
where qty < reorder_level;
This would return the following result:
PRODUCT_ID | QTY | REORDER_LEVEL |
3000 | 8 | 10 |
6000 | 4 | 5 |
However, if we wanted to see the products that were below their reorder levels as well as NULL reorder levels, we would use the LNNVL function as follows:
select * from products
where LNNVL(qty >= reorder_level);
This would return the following result:
PRODUCT_ID | QTY | REORDER_LEVEL |
1000 | 20 | NULL |
3000 | 8 | 10 |
6000 | 4 | 5 |
In this example, the result set also contains the product_id of 1000 which has a NULL reorder level.
