Examine the structure of the INVOICE table:
Exhibit:
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.