Examine the structure of the INVOICE table.
Name Null? Type
—————— ——————- ————-
INV_NO NOT NULL NUMBER(3)
INV_DATE DATE
INV_AMT NUMBER(10,2)
Which two SQL statements would execute successfully?
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;
A. NVL2(inv_date, ‘Pending’, ‘Incomplete’), where 2rd and 3rd parameters are from compatible data type and 1st can be autocasted to it.
C. NVL2(inv_date, sysdate-inv_date, sysdate), where 2rd and 3rd parameters are from compatible data type and 1st can be autocasted to it.
Only A will work fine
Just try
A
SELECT NVL2(sysdate, ‘Pending’, ‘Incomplete’)
FROM dual;
B.
SELECT NVL2(10, sysdate, ‘Not Available’)
FROM dual;
C.
SELECT NVL2(sysdate, sysdate-sysdate+5, sysdate)
FROM dual;
D.
SELECT NVL2(10, 10*.25, ‘Not Available’)
FROM dual;
C will work only in this case
SELECT NVL2(sysdate, sysdate-sysdate, sysdate)
FROM dual;
Option A & C are correct. In Option C, date-date returns the result in Julian date format. So both are compatible data types in given NVL2 statement.