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 threemandatory 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.
This implicit conversions always get me.
So, in order to make NVL2(column, arg1, agr2) work, arg1 and arg2 must have the same datatype, otherwise Oracle will try to do an implicit conversion.
“If arg1 is character data, then Oracle Database converts agr2 to the datatype of arg1 before comparing them unless arg2 is a null constant. In that case, a datatype conversion is not necessary. Oracle returns VARCHAR2 in the character set of arg1.”
“If arg1 is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.”
A – Both are character data, so it works.
B – Date x character data won’t work, unless the character data can be converted to date (e.g.: ’01-JAN-2000′), which is not the case.
C – *This one got me*. So, you have “SYSDATE – inv_date”, they’re both DATE but the expression gives you a NUMBER result, yes a NUMBER not a DATE, a number that represents the number of days, that’s called “Julian date”. And here’s the thing, Julian dates can be converted to a DATE implicitly. So arg2 could be DATE or NUMBER and would still work, but if it was SYSDATE as arg1 and “SYSDATE – inv_date” as arg2 instead, then an error would be raised as DATE cannot be converted to NUMBER, or precisely, to Julian date.
D – Number x character data won’t work, unless the character data is a number (e.g.: ‘2210’,’5121′).
Char can’t not convert to date or number.
Is it why where is char to_date/ to_number
and Date/number to_char