Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

LNNVL is an oracle built in function that returns TRUE for conditions evaluating to FALSE or UNKNOWN, and returns FALSE for conditions evaluating to TRUE. My question is what would be the benefit of returning the opposite of the truth condition rather than just handling the NULL values?

For example, suppose you have an Emp table with StartCommission and CurrentCommission columns which may contain nulls. The following returns only rows with neither value null:

SELECT * FROM Emp WHERE StartCommission = CurrentCommission;

If you wanted to include rows where either commission is null you could do something like this:

SELECT * FROM Emp WHERE StartCommission = CurrentCommission 
OR StartCommission IS NULL OR CurrentCommission IS NULL;

It would seem like a function would exist to shorten this syntax, but using LNNVL returns all the non-equal records and all the records with nulls.

SELECT * FROM Emp WHERE LNNVL(StartCommission = CurrentCommission);

Adding NOT to this only returns rows without nulls. It seems to me that the desired functionality for this case would be to keep true conditions true, false conditions false, and have unknown conditions evaluate to true. Have I really created a low use case here? Is it really more likely to want to turn unknown into true, true into false, and false into true?

create table emp (StartCommission Number(3,2), CurrentCommission Number(3,2));
insert into emp values (null,null);
insert into emp values (null,.1);
insert into emp values (.2,null);
insert into emp values (.3,.4);
share|improve this question
    
Today I met LNNVL and I'm trying to find out or understand this part as well "what would be the benefit of returning the opposite of the truth". However, the statement you use to exemplify LNNVL should use the not equal operator: SELECT * FROM Emp WHERE LNNVL(StartCommission <> CurrentCommission); so that it returns same result as the statement using ORs. In other words, if we are using a condition and we also want to include rows having NULL value, we need to pass the negate of our condition to LNNVL. –  Only You Jan 8 '13 at 19:46
    
Another simple example. Only employees who actually receive a commission of less than 20%: SELECT * FROM employees WHERE commission_pct < .2; To also include employees who receive no commission at all: SELECT * FROM employees WHERE LNNVL(commission_pct >= .2); –  Only You Jan 8 '13 at 19:49
    
@OnlyYou - You are correct, that to make LNNVL work the same the condition would have to be <>. I left it = more because if the function did return the same results with the = sign it would make more sense. Essentially the function is performing two actions 1. Null inclusion, 2. Boolean toggle. The former makes sense, the later just seems to confuse things requiring the opposite of the desired condition. i.e. = requires <>, < requires >=, etc. –  Leigh Riffel Jan 9 '13 at 13:41
add comment

2 Answers

up vote 4 down vote accepted

It is a weird function with a weird history - but then so is nvl2 weird. lnnvl is basically an is not true operator - no doubt it can be put to good use like nvl2 can, but when you have to look a function up every time you use it to remind you exactly what it does, you are left wondering if it is best to stick to nvl, coalesce, decode and nullif along with case expressions, which are more intuitive

share|improve this answer
    
I don't find NVL2 weird, but then I use it a lot and I've never used LNNVL. –  Leigh Riffel May 12 '11 at 20:49
    
@Leigh - I guess you have to use it just often enough for it to become not weird :) I never used nullif until I realised it can be a great help making 'division by zero' = null. Do you really find nvl(a,c,b) much better than decode(a,null,b,c)? –  Jack Douglas May 12 '11 at 20:59
    
I'm curious to know what else you can use it for? –  Jack Douglas May 13 '11 at 5:39
add comment

Put it this way, I've not yet once used LNNVL in my several years of being a DBA and PL/SQL Programmer. I have used NVL2 on occasion (and always had to look up which side was true, and which side wasn't). By that point, it seems better from a readability perspective to end up using NVL, DECODE, CASE, etc.,

Alternatively, this works, assuming one has a good handle on how Oracle handles NULLs and arithmetic, but by this point, one may as well as use your original query for readability (and the execution plan may take a harder hit, too):

/* Return all rows where StartCommission is the same
 * as Current Commission, or those rows who have a
 * NULL in either (including both)
 */

SELECT *
  FROM Emp
 WHERE StartCommission = CurrentCommission
    OR StartCommission + CurrentCommission IS NULL

-- NULL + NULL, or NULL + Number is always NULL; hence return either
-- those records that are equal, or have a combined total of NULL
-- (either or both fields will be NULL).
share|improve this answer
    
Do you mean StartCommission<>CurrentCommission OR StartCommission + CurrentCommission IS NULL? –  Jack Douglas May 13 '11 at 17:21
    
@JackPDouglas - It makes sense as =. –  Leigh Riffel May 13 '11 at 19:25
    
Interesting variation. I agree with your conclusions. –  Leigh Riffel May 13 '11 at 19:27
    
@Leigh - I see, an alternative to the function you want, not an alternative to lnnvl. –  Jack Douglas May 13 '11 at 20:27
    
@JackPDouglas - I understand and I think that is what Kerri intended. –  Leigh Riffel May 13 '11 at 21:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.