Oracle LNNVL function

Preamble

Oracle/PLSQL LNNVL function is used in the WHERE SQL query sentence to evaluate the state when one of the operands may contain the value NULL.

Oracle/PLSQL syntax of LNNVL function

LNNVL( condition_id )

The LNNVL function will return to the following:

So, if we had two columns called qty and reorder_level, where qty = 20 and reorder_level IS NULL, the function LNNVL would return the following:

LNNVL function in the following versions of Oracle/PLSQL

Oracle 12c, Oracle 11g, Oracle 10g

The LNNVL function can be used in Oracle PLSQL.

Let’s have a look at an example. If we had a product table containing the following data:

And we wanted to find all the products whose QTY was below REORDER_LEVEL, let’s run the next SQL query:

SELECT *
FROM prods
WHERE QTY < REORDER_LEVEL;

The request will return the following result:

However, if we wanted to consider products that were lower than REORDER_LEVEL and REORDER_LEVEL had the value NULL, we would use the function LNNVL as follows:

SELECT *
FROM prods
WHERE LNNVL(QTY >= REORDER_LEVEL);

This will return the next result:

In this example, the resulting set also contains prod_id 1000, which has REORDER_LEVEL NULL.

LNNVL FUNCTION IN ORACLE SQL

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​