Examine the structure of the INVOICE table.
Which two SQL statements would execute successfully? (Choose two.)
A.
SELECT inv_no,NVL2(inv_date,’Pending’,’Incomplete’)
FROM invoice;
B.
SELECT inv_no,NVL2(inv_amt,inv_date,’Not Available’)
FROM invoice;
C.
SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate)
FROM invoice;
D.
SELECT inv_no,NVL2(inv_amt,inv_amt*.25,’Not Available’)
FROM invoice;
Explanation:
The NVL2 Function
The NVL2 function provides an enhancement to NVL but serves a very similar purpose. It evaluates
whether a column or expression of any data type is null or not.
5-6 The NVL function\\
If the first term is not null, the second parameter is returned, else the third parameter is returned.
Recall that the NVL function is different since it returns the original term if it is not null. The NVL2
function takes three mandatory parameters. Its syntax is NVL2(original, ifnotnull, ifnull), where
original represents the term being tested. Ifnotnull is returned if original is not null, and ifnull is
returned if original is null. The data types of the ifnotnull and ifnull parameters must be compatible,
and they cannot be of type LONG.
They must either be of the same type, or it must be possible to convert ifnull to the type of the
ifnotnull parameter. The data type returned by the NVL2 function is the same as that of the ifnotnull
parameter.