what is the difference between NVL and NVL2 functions?
Question
TheDataMonk
Follow Me
About TheDataMonkGrand Master
I am the Co-Founder of The Data Monk. I have a total of 6+ years of analytics experience 3+ years at Mu Sigma 2 years at OYO 1 year and counting at The Data Monk I am an active trader and a logically sarcastic idiot :)Follow Me
Related questions
The Given Data set, Table has transitive dependencies. Explain Which Attributes are partially dependent. How will you overcome this? Explain the approach?
Write a query to Plot a Relationship between the highest followers in every country (Country Wise Highest Followers) and show which country has the highest followers and which country has the lowest one. Explain the approach?
Write a Query to Find the account with the highest number of followers in every country. Explain the approach?
Assign Rank To each Category by authentic engagement in each country and return the category with a rank less than 3. Explain the Approach?
Write a Query to check for Missing values in the given table? Explain how will you deal with the missing data for qualitative and quantitative data?
Answers ( 3 )
NVL() takes 2 parameters whereas NVL2()
takes 3 parameters
NVL(expr1,expr2)
expr1 is the source value or expression that may
contain null.
expr2 is the target value for converting the null.
If expr1 is null then it will be replaced with expr2 in that particular column
NVL2(expr1,expr2,expr3)
This function examines expr1.
If expr1 is not null, then expr2 is returned.
If expr1 is null, then expr3 is returned.
NVL = Null Value Functions
syntax :
NVL(EXPR1,EXPR2)
Bookish Definition: If expression1 is not NULL, then expression1 is returned. If expression1 is NULL then expression2 is returned. In general, the return type will be that of the first expression
Understand by Example.
NVL(‘student_marks’,0) : student_marks = 33 : o/p = 33
NVL(‘student_marks’,0) : student_marks = NULL : o/p = 0
NVL(‘student_marks’,NULL) : student_marks = 33 : o/p = 33
NVL(‘student_marks’,NULL) : student_marks = NULL : o/p = NULL
NVL2
syntax : NVL2(expression1, expression2, expression3)
Bookish definition :
Expression1 is the one that will be evaluated. If it is not null, then expression2 is returned. If it is null then expression3 is returned.
The first expression in NVL2 is never returned, it is only used to determine whether expression2 must be returned, or expression3.
Understand by Example :
NVL2(‘student_marks’,’student_rank’,’student_IQ’)
O/P : student_marks because first expression in NVL2 is never returned, it is only used to determine whether expression2 must be returned, or expression3.
SELECT NVL2(NULL, ‘student_rank’, ‘student_IQ’) FROM students
O/P : student_IQ
NVL2(‘student_marks’,’student_rank’,NULL)
O/P : student_rank
NVL2(‘student_marks’,NULL,’student_IQ’) ;
O/P : student_IQ
A) NVL(source_value,value_when_source_is_null) : It evaluates the source_value and if it is null, it returns the value provided in second argument of the function.
Eg: Select Name , NVL(dept,’Not Assigned’)
From Employees;
Output:
A|IT
B|HR
C|Not Assigned
B)NVL2(source_value,value_when_source_is_not_null,value_when_source_is_null) : It evaluates the source_value.If it is not null , it returns the value provided in second argument of the function and when it is null , third argument value is returned.
Eg: Select Name , NVL2(dept,’Assigned’,’Not Assigned’)
From Employees;
Output:
A|Assigned
B|Assigned
C|Not Assigned