GENERAL Functions in SQL are used to deal with the NULL values. The GENERAL Functions in SQL are NULLIF, DECODE, NVL, COALESCE, NVL2, LNNVL, NANVL. These GENERAL Functions are single row functions that these returns single value for a row in result set. In some functions are explained in the previous topics. Now we want to know about NVL2, LNNVL, NANVL functions. These functions works on ORACLE database only. These functions are explained as follows –

SELECT NVL2(Expression1,Expression2,Expression3)FROM table_name; 

SELECT column1,column2,… FROM table_name WHERE LNNVL(conditions); 

SELECT column1,column2,..NANVL(column1,value)FROM table_name; 

NVL2 Function –

NVL2 Function checks the first expression. If first expression have NOT NULL values NVL2 returns second expression. If first expression have NULL values NVL2 returns third expression. In simple if first expression contain NOT NULL values it returns second expression, If first expression contains NULL values it returns third expression. The First expression may have any datatype.

LNNVL Function –

LNNVL Function estimates the condition when both operands are NULL. LNNVL Function takes arguments as condition. LNNVL Function used in the WHERE class of SELECT statement. If the condition is true, LNNVL Function returns unknown or false. If the condition is false, LNNVL Function returns true.

NANVL Function –

NANVL Function is used for floating-point numbers like BINARY_FLOAT, BINARY_DOUBLE. NANVL Function tells database that it returns an alternative value for input value that it consists a NaN (not a number). Input value does not consists NaN then it returns the existing input value.

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

Scenario – Getting rows by using NVL2 Function.

Requirement –Getting rows from employee_detsils table by using NVL2 Function. The query was as follows –

SELECT emp_id,NVL2(manager_id,emp_name,designation)
FROM employee_detsils; 

By executing above query, we can get designation values in the manager_id for first two rows because those contains NULL values, rest of the rows get emp_name values. The output was as follows –

Scenario –Getting output by using LNNVL Function.

Requirement – Getting rows from employee_details table by using LNNVL Function.
The query was as follows –

SELECT COUNT(*) AS TotalEmployees FROM  employee_details WHERE
LNNVL( salary >1000); 

By executing above query, we can get all employees in the employee_details table. The output was as follows –

Scenario – Getting output by using NANVL Function.

Requirement – Getting rows from employee_details table by using NANVL Function.
The query was as follows –

Let us consider we have a table as follows –

SELECT NANVL( Bin_DOUBLE, 0) FROM test_table; 

By executing above query, we can get output like as shown in below -