Oracle SQL Function LNNVL

This is a very good function. Here is the oracle documentation for this function. This function can be used for these purposes too

1. You want to confuse others
2. You want others not to touch your code

Elaborating the example given in the above documentation, the query

SELECT COUNT(*) FROM employees WHERE LNNVL(commission_pct >= .2);

is equivalent to

SELECT COUNT(*) FROM employees WHERE commission_pct < .2 or commission_pct is NULL;

I think this function can be used wherever we need to do (:bind_var is NULL or :bind_val <condition> <value>).

if we replace the above condition with LNNVL function then we have to make sure that condition in LNNVL is exact opposite.

Some example of this function are:(to be added later)

Condition WITHOUT LNNVL	Condition WITH LNNVL