A) SELECT NVL(cust_credit_limit * .15, 'Not Available') FROM customers;
B) SELECT NVL2(cust_credit_limit * .15, 'Not Available') FROM customers;
C) SELECT NVL(TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;
D) SELECT TO_CHAR(NVL(cust_credit_limit * .15, 'Not Available')) FROM customers;
E) SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;
Correct C & E.
C, E are correct. Reason why A is not correct is NVL function expects the cust_credit_limit column to be of same datatype as Replacement value ('Not Available' here). So it has to be converted to string (TO CHAR) to achieve it.
Result will be like:
SQL> SELECT NVL(cust_credit_limit * .15, 'Not Available') FROM customers;
SELECT NVL(cust_credit_limit * .15, 'Not Available') FROM customers
*
ERROR at line 1:
ORA-01722: invalid number
SQL> SELECT NVL(TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;
NVL(TO_CHAR(CUST_CREDIT_LIMIT*.15),'NOTA
----------------------------------------
150
750.0375
Not Available
SQL> SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;
NVL2(CUST_CREDIT_LIMIT,TO_CHAR(CUST_CRED
----------------------------------------
150
750.0375
Not Available
You can see that in the First Query 'Not Available' is not accepted as a Replacement for the Column with Number Datatype. So once Number Datatype is converted into CHAR, the 'Not Available' string gets displayed.
C. SELECT NVL(TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;
This query will return the value of the cust_credit_limit column multiplied by 0.15, converted to a character value, if the cust_credit_limit column is not null. Otherwise, it will return the string 'Not Available'.
E. SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;
This query will return the value of the cust_credit_limit column if it is not null. Otherwise, it will return the value of the expression TO_CHAR(cust_credit_limit * .15), converted to a character value. If the expression TO_CHAR(cust_credit_limit * .15) is null, the NVL2() function will return the string 'Not Available'.
C, E are correct. Reason why A is not correct is NVL function expects the cust_credit_limit column to be of same datatype as Replacement value ('Not Available' here). So it has to be converted to string (TO CHAR) to achieve it.
Result will be like:
SQL> SELECT NVL(cust_credit_limit * .15, 'Not Available') FROM customers;
SELECT NVL(cust_credit_limit * .15, 'Not Available') FROM customers
*
ERROR at line 1:
ORA-01722: invalid number
SQL> SELECT NVL(TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;
NVL(TO_CHAR(CUST_CREDIT_LIMIT*.15),'NOTA
----------------------------------------
150
750.0375
Not Available
SQL> SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;
NVL2(CUST_CREDIT_LIMIT,TO_CHAR(CUST_CRED
----------------------------------------
150
750.0375
Not Available
You can see that in the First Query given below that 'Not Available' is not accepted as a Replacement for the Column with Number Datatype. So once Number Datatype is converted into CHAR, the 'Not Available' string gets displayed.
If the Replacement Value is a Number instead of 'Not Available', NVL will accept it since the cust_credit_limit column's datatype is Number.
Nope, C, E are correct. You cannot convert all the function NVL in D, because its result is an error. But, in E, we have a similar situation as in C.
upvoted 2 times
...
...
This section is not available anymore. Please use the main Exam Page.1z0-082 Exam Questions
Log in to ExamTopics
Sign in:
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
you1234
Highly Voted 4 years, 5 months agoEkos
4 years, 1 month agoama
4 years, 4 months agovaliantvimal
Most Recent 7 months, 4 weeks agovaliantvimal
7 months, 4 weeks agovaliantvimal
7 months, 4 weeks agoLalala8
1 year, 2 months agoj_tw
1 year, 8 months agoauwia
1 year, 5 months agovaliantvimal
7 months, 4 weeks agovaliantvimal
7 months, 4 weeks agovaliantvimal
7 months, 4 weeks agoXhostSI
2 years, 10 months agoama
4 years, 5 months agoLrnsTgh
3 years, 3 months agoamorimleandro
4 years, 5 months ago