Exam 1z0-061 topic 2 question 71 discussion

Actual exam question from Oracle's 1z0-061

Question #: 71
Topic #: 2

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;

Show Suggested Answer

Suggested Answer: AC 🗳️
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.

by hggz at Sept. 20, 2019, 7:05 p.m.

Comments

Submit Cancel

hggz

5 years, 8 months ago

Answer is correct but Explanation - no. NVL2 (expr1,expr2,expr3) The argument expr1 can have any datatype. The arguments expr2 and expr3 can have any datatypes except LONG. If the datatypes of expr2 and expr3 are different: * If expr2 is character data, then Oracle Database converts expr3 to the datatype of expr2 before comparing them unless expr3 is a null constant. In that case, a datatype conversion is not necessary. Oracle returns VARCHAR2 in the character set of expr2. * If expr2 is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype. https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions106.htm

upvoted 2 times

...

Exam 1z0-061 All Questions

View all questions & answers for the 1z0-061 exam

Exam 1z0-061 topic 2 question 71 discussion

Comments

hggz

SY0-701