Week 5 Tutorial
NVL and NVL2
As we have already discussed, a database is a repository for data. However, it wouldn’t do much good to
deposit information in a database if you could not retrieve the data in a meaningful format when it was
needed. This week you will learn some new SQL commands that will enable you to retrieve data and
have the output formatted in a way that is meaningful to the user.
NVL and NVL2
A couple of weeks ago, we had a great deal of discussion around the use of NULLs in a database.
Whether you choose to allow them in a database you build or not, you will inevitably have to deal with
them in someone else's database. Two very useful NULL functions are NVL and NVL2.
The NVL function basically says, “if no value is returned, then use this value instead.” For example, if I
have an Employee table and the termination date is null for employees that have not yet been terminated,
then I could substitute a date far into the future like this:
SELECT EmployeeID, NVL(TerminationDate, '01-DEC-2300')
This will return the termination date of employee's that have termed and will return 01-DEC-2300 for those
that have not.
NVL2 is a little different. In this case, we can evaluate the data and pick two return values - one if it is not
null and one if it is null. To translate it into English, NVL2 says, “if there is a value in my column, display
this, but if there is not a value in my column, then display that.” You start by listing the column to be
evaluated, followed by the value you want to display if not null and then the value if null. For example,
using the same TerminationDate, I could do the following:
SELECT EmployeeID, NVL2(TerminationDate, 'Still on the payroll', 'Terminated')
In SQL*Plus, you are always required to use a FROM clause in your SELECT statement. But what do you
do if you do not have a table you are selecting from? For example, if I want to return the system date, I
would SELECT SYSDATE. However, I still need a FROM clause. Therefore, Oracle provides a special