Get Unlimited Contributor Access to the all ExamTopics Exams!
Take advantage of PDF Files for 1000+ Exams along with community discussions and pass IT Certification Exams Easily.
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'.
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.
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
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.
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, 10 months agoama
3 years, 9 months agoEkos
3 years, 6 months agovaliantvimal
Most Recent 2 weeks, 3 days agovaliantvimal
2 weeks, 3 days agovaliantvimal
2 weeks, 3 days agoLalala8
7 months, 2 weeks agoj_tw
1 year agovaliantvimal
2 weeks, 3 days agovaliantvimal
2 weeks, 3 days agovaliantvimal
2 weeks, 3 days agoauwia
10 months, 2 weeks agoXhostSI
2 years, 2 months agoama
3 years, 10 months agoamorimleandro
3 years, 10 months agoLrnsTgh
2 years, 8 months ago