Connect to your Oracle database and try the following:
SELECT 'hi there' AS mycol FROM DUAL WHERE lnnvl (1 = 2)
You should get :
MYCOL ---------- hi there
Now, you ask, what is LNNVL? You search the Oracle documentation, but you cannot find any mention of it in releases prior to 10gR1, yet you successfully execute the above query in your Oracle 9.2 (or even 8.1.7) database!
LNNVL had been an undocumented SQL function until Oracle database version 10gR1. Let’s go back in time a bit and read what Jonathan Lewis and Lex de Haan wrote about it:
From: Jonathan Lewis
Date: Sun, Nov 2 2003 10:18 am
lnnvl(predicate) is true if predicate is false or null. I can’t say "for sure", but I believe it appeared somewhere around 7.3, or maybe 7.2 to handle problems arising from the SQL generated by a query co-ordinator for its parallel query slaves when Oracle was trying to get partition elimination on partition views. (And if it really is there for PQ problems, I’d second Richard’s advice – don’t use it, you never know when it’s going to disappear).
From: Lex de Haan
Date: Sat, 29 May 2004 21:27:53
The ANSI/ISO SQL standard offers the three operators you need: "IS TRUE", "IS FALSE", and "IS UNKNOWN". But as far as I know, no vendor has implemented these. Note the power of these operators; they accept a Boolean argument. The "IS NULL" becomes very clumsy as soon as multiple predicates are involved. One nice Oracle function (that has been around for quite a while, but was undocumented until 10g) is the LNNVL function.
The Oracle’s LNNVL function:
So, if you’re stuck with Oracle 8i or 9i, you can safely use the undocumented LNNVL function, knowing that it has become documented and supported in 10g and above.
Related Oracle documentation.