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
...
...
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 3 years, 11 months agoEkos
3 years, 8 months agoama
3 years, 11 months agovaliantvimal
Most Recent 2 months, 2 weeks agovaliantvimal
2 months, 2 weeks agovaliantvimal
2 months, 2 weeks agoLalala8
9 months, 2 weeks agoj_tw
1 year, 2 months agoauwia
1 year agovaliantvimal
2 months, 2 weeks agovaliantvimal
2 months, 2 weeks agovaliantvimal
2 months, 2 weeks agoXhostSI
2 years, 4 months agoama
4 years agoLrnsTgh
2 years, 10 months agoamorimleandro
3 years, 12 months ago