• Skip to primary navigation
  • Skip to main content

Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

  • Home
  • HTML
    • Tags in HTML
    • HTML Attributes
  • OA Framework
    • Item in OAF
    • Regions in OAF
    • General OAF Topics
  • Oracle
    • statement
    • function
    • clause
    • plsql
    • sql
You are here: Home / Oracle / function / LNNVL Function in Oracle SQL – PLSQL

LNNVL Function in Oracle SQL – PLSQL

November 28, 2012 by techhoneyadmin

The LNNVL function in Oracle SQL / PLSQL is used in the WHERE Clause of an SQL / PLSQL SELECT Statement. LNNVL Function is used to evaluate a condition where one of the operands of the condition may have NULL value.

Syntax for the LNNVL function in Oracle SQL / PLSQL is:

SELECT column(s)
FROM table_name
WHERE LNNVL (condition);

LNNVL Returns the values based on the table below:

Condition_Evaluation_Value LNNVL Return
TRUE FALSE
FALSE TRUE
UNKNOWN TRUE

Example:

Suppose we have a table named ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support 5
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 5
107 Emp G 12000 Sales
108 Emp H 12000 Support

If we write our query as:

 SELECT *
FROM employee
WHERE commission < 15;

We will get the following output:

Employee_ID Employee_Name Salary Department Commission
104 Emp D 30000 Support 5
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 5

Suppose we wish to see the records where commission is less than equal to 15 and also could be NULL, then we can achieve the same using the LNNVL function as:

 SELECT *
FROM employee
WHERE LNNVL(commission >= 15);

We will get the following output:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales  
104 Emp D 30000 Support 5
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 5
107 Emp G 12000 Sales  
108 Emp H 12000 Support  

In the above fetched results observe that the records for NULL commission are also included.


Filed Under: function Tagged With: how to use LNNVL function in oracle database query, how to use LNNVL function in oracle plsql, how to use LNNVL function in oracle sql, LNNVL function in oracle plsql, LNNVL function in oracle sql, LNNVLPLSQL, syntax and example of LNNVL function in oracle database query, syntax and example of LNNVL function in oracle plsql, syntax and example of LNNVL function in oracle sql, using LNNVL function in oracle database query, using LNNVL function in oracle plsql, using LNNVL function in oracle sql

Copyright © 2023 · Parallax Pro on Genesis Framework · WordPress · Log in