{"id":16,"date":"2020-01-03T12:00:41","date_gmt":"2020-01-03T12:00:41","guid":{"rendered":"https:\/\/molecularsciences.org\/content\/?p=16"},"modified":"2024-02-09T13:39:49","modified_gmt":"2024-02-09T18:39:49","slug":"oracle-nvl-and-nvl2-functions","status":"publish","type":"post","link":"https:\/\/molecularsciences.org\/content\/oracle-nvl-and-nvl2-functions\/","title":{"rendered":"Oracle NVL and NVL2 functions"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\"><strong>NVL function<\/strong><\/h2>\n\n\n\n<p>NVL function can be used to substitute a value when null in encountered. It has the following syntax:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>NVL(string, replacement_string)<\/code><\/pre>\n\n\n\n<p>If string has a value, it will be displayed. If the string is null, the replacement string will be displayed.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select NVL(salary, 'unpaid volunteer') from staff<\/code><\/pre>\n\n\n\n<p>This query prints staff salaries. Volunteers do not have a salary so the value for their salary is null. In the query, when the salary is null, replacement string &#8216;unpaid volunteer&#8217; is printed.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select NVL(salary, monthly_invoice) from worker<\/code><\/pre>\n\n\n\n<p>A paid worker can be an employee or a contractor. An employee receives a monthly salary and a contractor invoices his hours of work at the end of the month. In this query, if the salary is not null, the salary is printed. If the salary is null, the monthly_invoice is printed.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select distinct id, NVL((select distinct 'Y' from table1 where x = y), 'N') active where c = d      <\/code><\/pre>\n\n\n\n<p>NVL function can also be used in a subquery as in the example above.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">NVL2 function<\/h2>\n\n\n\n<p>NVL2 function extends the funcitonality of NVL function. NVL function prints the value of the variable unless a null value is encountered; in which case a value is substituted for the null. In addition to this, NVL2 also allows you to substitute a value if the value of the variable is not null. Syntax:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>NVL2(variable, not_null_substitute, null_substitute)<\/code><\/pre>\n\n\n\n<p>Example<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select id, name, NVL2(phone, 'Provided', 'Not Provided') from staff_listing;<\/code><\/pre>\n\n\n\n<p>Here the NVL2 function prints Provided if a phone number is provided and Not Provided in the value is null.<\/p>\n\n\n\n<p>NVL2 function maintains the same functionality in Oracle 8i, 9i, 10g, 11g, and it is expected to remain the same in future versions.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>NVL function NVL function can be used to substitute a value when null in encountered. It has the following syntax: If string has a value, it will be displayed. If the string is null, the replacement string will be displayed. This query prints staff salaries. Volunteers do not have a salary so the value for [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16,515],"tags":[14,3],"class_list":["post-16","post","type-post","status-publish","format-standard","hentry","category-database","category-oracle","tag-database","tag-oracle"],"_links":{"self":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/16","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/comments?post=16"}],"version-history":[{"count":1,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/16\/revisions"}],"predecessor-version":[{"id":17,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/16\/revisions\/17"}],"wp:attachment":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/media?parent=16"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/categories?post=16"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/tags?post=16"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}