What is the importance of NVL and OLC
Answers
Explanation:
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.