Oracle DECODE vs NVL

Question

I am trying to parse an existing Oracle query written by a retired developer. I'm not very good at Oracle, and I am a little confused about using this function DECODEin the application Microfocus COBOL(where :BV-POS_YEARis the variable set for the year):

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

I am trying to understand how this will differ from:

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

I don’t understand something about functions DECODEor NVL? The developer is aware of the NVL function because it is used elsewhere in the select statement itself.

Answer 1

NVLreplaces NULLwith the specified value.

DECODE . , DECODE . ,

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

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. NVL DECODE NULL , .

NVL, NULL, .