NVL2 is the second null function in the series of Null function. We can say it’s a null function like NVL but on steroids as NVL2 expands the functionality of NVL function. NVL2 function lets you substitute the values when a null value or a non-null value is encountered.
NVL2 (Expression, value1, value2)
Unlike NVL null function NVL2 function has 3 parameters. Expression, value1 and value 2.
First parameter Expression can either be column name or an arithmetic expression whose result will be evaluated, whereas the second parameter is the value which will be returned if the first parameter value is Not Null. Furthermore if the first parameter value is Null the NVL2 returns the third parameter.
In Simple words if the data returned from first parameter is Not Null then value from the second parameter will be displayed otherwise value from third parameter will be returned.
SELECT commission_pct, NVL2 (commission_pct, 'Not Null', 0) FROM employees WHERE salary>13000;
On execution this query will return values in two columns where first column will display the actual values from commission pct column and second will display the string Not Null where some actual values exist in commission pct otherwise it will display 0 in place of Null.
Now if you want to display the actual value of commission pct column instead of Not Null string then you can simply write the column name which is commission pct at the place of this string.
Let me show you how
SELECT commission_pct, NVL2(commission_pct, commission_pct,0)
FROM employees WHERE salary>13000;
Now you have to comment on my YouTube or tweet me on my twitter and tell me. According to you what are the applications or say uses of this particular function NVL2? Where and how you can use this function? And if you have a crazy query with this NULL function NVL2 you can also comment that in the comment section below.
That’s all guys. Hope this article was useful. Kindly please share it on your social networking and help me reach out to more people. Thanks and have a great day!
SQL Script and Presentation used
You can DOWNLOAD SQL script and presentation used in the Video and in this article.
SQL Script used in Video and in this article
I have used copy cloud to share these resourses. You can also join copy cloud and get 15GB free cloud storage for lifetime. If you will use this referal link https://copy.com?r=j7eYO7
You will get 5GB extra free cloud storage means total 20GB free cloud storage.