DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Why Database Migrations Take Months and How to Speed Them Up
  • Unmasking Entity-Based Data Masking: Best Practices 2025
  • How Trustworthy Is Big Data?
  • Fixing Common Oracle Database Problems

Trending

  • DGS GraphQL and Spring Boot
  • How to Convert XLS to XLSX in Java
  • Unlocking AI Coding Assistants: Generate Unit Tests
  • Unlocking the Potential of Apache Iceberg: A Comprehensive Analysis
  1. DZone
  2. Data Engineering
  3. Databases
  4. Two Practical Examples of the Oracle NVL Function

Two Practical Examples of the Oracle NVL Function

The NVL function is helpful when you want to store the fact that there is no data for a particular column, but you want to display something else.

By 
Ben Brumm user avatar
Ben Brumm
·
Mar. 14, 17 · Opinion
Likes (2)
Comment
Save
Tweet
Share
87.7K Views

Join the DZone community and get the full member experience.

Join For Free

In this article, I'll explain what the Oracle NVL function does and show a few examples of using it.

The NVL Function

In Oracle, the NVL function allows you to replace a NULL value with another value.

It's helpful when you want to store the fact that there is no data for a particular column, but you want to display something else.

I'll show you a few examples of this.

Example 1: End Date

A common concept in database tables is to store a start and end date for a record, which is used to track when a record is effective for.

In some cases, the end date is set to NULL if there is no end date or if it is the active record.

Take a look at this table here:

CUSTOMER_ID CUSTOMER_NAME STATUS START_DATE END_DATE
1 ABC Prospect 1-Jan-17 10-Jan-17
2 ABC Signed 11-Jan-17 (null)
3 DJ Signed 1-Jan-17 (null)
4 WXY In Progress 26-Feb-17 (null)
5 FED Signed 21-Dec-17 3-Feb-17
6 FED (null) 4-Feb-17

15-Feb-17

We can see that there are a few records for this customer. The start date indicates when the record has started, and the end date indicates when the record has ended.

The active record is where the end date is NULL.

We could use a query like this:

SELECT customer_id, 
       customer_name, 
       status, 
       start_date, 
       end_date 
FROM   customer_history; 

However, we might not want to display NULL or an empty value in the system that uses this data (i.e., a report or a website).

So, we can use the NVL function. We first need to work out what value we want to display. This could be a static date (i.e., 31-DEC-9999), for example.

Here's an example using a static date:

SELECT customer_id,
       customer_name,
       status,
       start_date,
       NVL(end_date, '31-DEC-9999')
FROM customer_history;


CUSTOMER_ID CUSTOMER_NAME STATUS START_DATE NVL(END_DATE,'31-DEC-9999')
1 ABC Prospect 1-Jan-17 10-Jan-17
2 ABC Signed 11-Jan-17 31-Dec-99
3 DJ Signed 1-Jan-17 31-Dec-99
4 WXY In Progress 26-Feb-17 31-Dec-99
5 FED Signed 21-Dec-17 3-Feb-17
6 FED (null) 4-Feb-17

15-Feb-17

As you can see, the NVL function can be used to translate a NULL date value to something else.

2. Missing Data That Should Be Populated

Another example of using the NVL function is where you are missing data for a column that you or the application believes should be there or if you're loading data from one table into another table and cannot use NULL values for some reason.

Let's say we have the same table as above.

In this table, we see information on customers. However, let's say that we use this to populate another table in another system or a materialized view and the status column cannot be NULL.

A business rule specifies that we need a value for status, but we can't update the underlying table.

So, we can use the NVL function here.

SELECT customer_id, 
       customer_name, 
       Nvl(status, 'Unknown') AS status, 
       start_date, 
       end_date 
FROM   customer_history; 


CUSTOMER_ID CUSTOMER_NAME STATUS START_DATE END_DATE
1 ABC Prospect 1-Jan-17 10-Jan-17
2 ABC Signed 11-Jan-17 (null)
3 DJ Signed 1-Jan-17 (null)
4 WXY In Progress 26-Feb-17 (null)
5 FED Signed 21-Dec-17 3-Feb-17
6 FED Unknown 4-Feb-17

15-Feb-17

As you can see, the query now shows a value for the status column, even if the original value was NULL. This now satisfies the business rule that says the status column cannot be null in your new table.

Now you know a couple of examples on how to use the NVL function in Oracle.

Database

Opinions expressed by DZone contributors are their own.

Related

  • Why Database Migrations Take Months and How to Speed Them Up
  • Unmasking Entity-Based Data Masking: Best Practices 2025
  • How Trustworthy Is Big Data?
  • Fixing Common Oracle Database Problems

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: