Summary -

In this topic, we described about the General Functions with detailed example.

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 –

Syntax for NVL2 -

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

Syntax for LNNVL –

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

Syntax for NANVL –

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.

Example –

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

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

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 –

emp_id manager_id
001 Director
002 Director
003 Employee3
004 Employee4
005 Employee5
006 Employee6
007 Employee7
008 Employee8
009 Employee9

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 –

8.592E+613 5.872E+193
NAN 2.589E+078

SELECT NANVL( Bin_DOUBLE, 0) FROM test_table; 

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

8.592E+613 5.872E+193
0 2.589E+078