Oracle DECODE vs. NVL

Question

I'm trying to analyze an existing Oracle query written by a departed developer. I'm not well versed in Oracle and I'm a bit confused by the use of this DECODE function in a Microfocus COBOL app (where :BV-POS_YEAR is a variable set to a year):

SELECT ...., DECODE(DELV_YEAR, NULL, :BV-POS_YEAR, DELV_YEAR), ....

I'm trying to understand how that would be different from:

SELECT ...., NVL(DELV_YEAR, :BV-POS_YEAR), ....

Am I misunderstanding something about the DECODE or NVL functions? The developer is aware of the NVL function as it is used elsewhere in the very same select statement.

Answer 1

Let's look at some Oracle NVL function examples and explore how to use the NVL function in Oracle/PLSQL.

For example:

SELECT NVL(supplier_city, 'n/a')
FROM suppliers;

The SQL statement above would return 'n/a' if the supplier_city field contained a null value. Otherwise, it would return the supplier_city value.

Another example using the NVL function in Oracle/PLSQL is:

SELECT supplier_id,
NVL(supplier_desc, supplier_name)
FROM suppliers;

This SQL statement would return the supplier_name field if the supplier_desc contained a null value. Otherwise, it would return the supplier_desc.