Examine the structure and data in the PRICE_LIST table:
name Null Type
PROD_ID NOT NULL NUMBER(3)
PROD_PRICE VARCHAR2(10)
PROD_ID PROD_PRICE
100 $234.55
101 $6,509.75
102 $1,234
You plan to give a discount of 25% on the product price and need to display the discount amount in the
same format as the PROD_PRICE.
Which SQL statement would give the required result?
A.
SELECT TO_CHAR(prod_price* .25,’$99,999.99′)
FROM PRICE_LIST;
B.
SELECT TO_CHAR(TO_NUMBER(prod_price)* .25,’$99,999.00′)
FROM PRICE_LIST;
C.
SELECT TO_CHAR(TO_NUMBER(prod_price,’$99,999.99′)* .25,’$99,999.00′) FROM PRICE_LIST;
D.
SELECT TO_NUMBER(TO_NUMBER(prod_price,’$99,999.99′)* .25,’$99,999.00′) FROM PRICE_LIST;
Why it cant be D?
TO_NUMBER(prod_price,’$99,999.99′)* .25 is already a number. We do not need to convert it to number any more but it is needed to display the number in certain format. For this to_char is used.
But it wont give any error right?
You get an error. See the steps:
SELECT TO_NUMBER(prod_price,’$99,999.99′) FROM products;
TO_NUMBER(PROD_PRICE,’$99,999.99′)
———————————-
234.55
1234
6609.75
SELECT TO_NUMBER(prod_price,’$99,999.99′)*0.25 FROM products;
TO_NUMBER(PROD_PRICE,’$99,999.99′)*0.25
—————————————
58.6375
308.5
1652.4375
SELECT to_number(TO_NUMBER(prod_price,’$99,999.99′)*0.25,’$99,999.00′) FROM products;
SQL Error: ORA-01722: invalid number
01722. 00000 – “invalid number”
*Cause:
*Action:
Also check for the opt B
Opt B wont work
In B format is missing for TO_NUMBER function.
Do you have any other material for the exam?
a,b, correct
c d.incorrect
please check and explain why?
Prod Price is of type Varchar and you want to multiply it with .25, so you need to convert it to a number type with a format before multiplying.
Hence opt c is correct.
B should be correct.
For to_number function, the format_mask is optional.
The format that will be used to convert char to a number.
Syntax (per Oracle Doc)
TO_NUMBER(text-exp, [fmt,] [nlsparams])
Thus, TO_NUMBER should work without format mask.
So how Oracle database knows what ‘$1,234’ means??? How to ‘understand’ the text if you do not use format in that case?
oh yeah, you right Justyna, thanks!
Why you would convert to a number with a format like ‘$’ ?
Check this:
select to_number(‘$234.55′,’$99.999.99’) from dual;
ORA-01481: invalid number format model
01481. 00000 – “invalid number format model”
*Cause: The user is attempting to either convert a number to a string
via TO_CHAR or a string to a number via TO_NUMBER and has
supplied an invalid number format model parameter.
*Action: Consult your manual.
I’m i missing something?
$99.999.99 <- you missed a comma 99,999.99 should work.
y not b?explain plz
Please see the above comments. Your answer is there.
B. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25,’$99,999.00′)
FROM PRICE_LIST;
The TO_NUMBER() function is missing the mask. It should be TO_NUMBER(prod_price,’$99,999.99′).
A,B is correct Ans.
For me only the first query is working.
Then why A is wrong…?
SQL> desc test;
Name Null? Type
—————————————– ——– —————————-
PROD_ID NUMBER(10)
PROD_PRICE VARCHAR2(10)
SQL> select to_char( prod_price*.25,’$99,9999,99.99′) from test;
TO_CHAR(PROD_PR
—————
$5,3310,78.50
$5,3560,78.50