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

NVL replaces NULL by specified value.

DECODE replaces any specified value by desired value and can be chained. Your usage is equivalent, but DECODE has much wider usage. For example you can write this code

SELECT supplier_name,
DECODE(supplier_id, 10000, 'IBM',
                    10001, 'Microsoft',
                    10002, 'Hewlett Packard',
                    'Gateway') result
FROM suppliers;

Which is equivalent to this pseudo code

if (supplier_id == 10000)
{
    SELECT 'IBM'
} else if (supplier_id == 10001)
{
    SELECT 'Microsoft'
} else if (supplier_id == 10002)
{
    SELECT 'Hewlet Packard'
} else
{
    SELECT 'Gateway'
}

EDIT: Performance of the NVL and DECODE for the NULL replacement was measured by users and it seems to be almost equal.

I personally advice to use NVL when you need to do the NULL replacement, because it's a bit more expressive.