Recently, I came across LNNVL Oracle function and decided to assess its usability.
According to Oracle documentation, it “provides a concise way to evaluate a condition when one or both operands of the condition may be null.”
Let’s see a couple of problems this function can be applied to:
For each department count the number of employees who get no commission.
There are over 10 possible solutions that I am aware of, I will focus only on a couple where we can leverage LNNVL function.
Solution #1. Filter with LNNVL in WHERE clause:
SELECT deptno, COUNT(*) cnt FROM scott.emp WHERE LNNVL(comm>0) GROUP BY deptno ORDER BY 1;
DEPTNO CNT ------ ---------- 10 3 20 5 30 3
In the above query, LNNVL(comm>0) filter is equivalent to: (comm<=0 OR comm IS NULL) and since comm cannot be negative, we can say that it is the same as NVL(comm,0)=0.
According to that same Oracle documentation, LNNVL “can be used only in the
WHERE clause of a query.” This does not seem to be true, at least in 12g+ releases:
Solution #2. Using LNNVL in conditional aggregation:
SELECT deptno, SUM(CASE WHEN LNNVL(comm>0) THEN 1 ELSE 0 END) cnt FROM scott.emp GROUP BY deptno ORDER BY 1;
LNNVL can also be used with IN operator. We will illustrate it with a solution (one of the many) to the following problem:
List all employees who is not a manager of somebody else.
SELECT empno, ename, deptno, job, mgr FROM scott.emp WHERE LNNVL(empno IN (SELECT mgr FROM scott.emp)) ORDER BY 1;
The following boolean expression defines a condition to see managers only:
empno IN (SELECT mgr FROM scott.emp)
We need the opposite one that handles NULLs – as mgr is a nullable column. And this is where LNNVL helps us.
If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/
Would you like to read about many more tricks and puzzles?
For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.