• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Sql » NVL2 function in Oracle

NVL2 function in Oracle

June 10, 2019 by techgoeasy Leave a Comment

This SQL tutorial provides explanations, examples for NVL2 function in Oracle

NVL2 function is a nesting function .It examines the first impression, if the the first impression is not null ,then NVL2 function returns the second expression. if the first impression is null, then it return the third expression.

NVL2 function in Oracle

expr1: it is the source values or expression which may contain null. You can provide column name,function on the column name
expr2: This is the value of the expression which is returned if the expr1 is not null
expr3: This is the value of the expression which is returned if the expr1 is null

The argument expr1 can have any datatype. The arguments expr2 and expr3 can have any datatypes except LONG.

SQL> select nvl2(user_name,1,2) from apps.fnd_user where rownum < 5;

NVL2(USER_NAME,1,2)
-------------------
1
1
1
1

SQL> select nvl2(end_date,1,2) from apps.fnd_user where rownum < 5;

NVL2(END_DATE,1,2)
------------------
1
2
1
1

If the datatypes of expr2 and expr3 are different:

If expr2 is character data, then Oracle Database converts expr3 to the datatype of expr2 before comparing them unless expr3 is a null constant. In that case, a datatype conversion is not necessary. Oracle returns VARCHAR2 in the character set of expr2.

If expr2 is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.

The data type of the return type is always the same as the data type of expr2 unless expr2 is character data, which case return value is always varchar2

SQL> select nvl2(end_date,1,'a') from apps.fnd_user where rownum < 5;
select nvl2(end_date,1,'a') from apps.fnd_user where rownum < 5
*
ERROR at line 1:
ORA-01722: invalid number

SQL> select nvl2(end_date,'a',1) from apps.fnd_user where rownum < 5;

N
-
a
1
a
a

Usage 

SELECT last_name, salary, NVL2(commission_pct,
salary + (salary * commission_pct), salary) income
FROM employees WHERE last_name like 'B%'
ORDER BY last_name;

Related Articles

Single row functions in Sql
NULLIF function in Oracle
Update statement in oracle
Coalesce Function in Oracle
Oracle LISTAGG Function
oracle date functions

Filed Under: Oracle, Oracle Sql Tagged With: NVL2 function in Oracle

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to generate tkprof in EBS in 19c
  • Oracle tkprof utility
  • What is Skew in Database?
  • how to find file based on size in linux
  • SQL trace, 10046 event in Oracle

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us