sri rao
asked on
Oracle + plsql
I've a small doubt, if any body knows pls give me some solution.
In my oracle table i've 2 columns AMOUNT & DECIMAL. So based on the decimal value, i've to convert the amount as mentioned in the FINAL_RESULT colum.
Ex: For amount 10000 its decimal places are 2, so i've give final_result as 100.00
see the following data,
Amount Decimal Final_Result
------ ------- --------------
10000 2 100.00
19388 3 19.388
344334 4 34.4334
Like that n no. of Amounts are there & n no. of decimal are there
How can i achieve the above Final_Result based on the Amount & Decimal columns.
Its very urgent......... reply pls. Give me the sol....
In my oracle table i've 2 columns AMOUNT & DECIMAL. So based on the decimal value, i've to convert the amount as mentioned in the FINAL_RESULT colum.
Ex: For amount 10000 its decimal places are 2, so i've give final_result as 100.00
see the following data,
Amount Decimal Final_Result
------ ------- --------------
10000 2 100.00
19388 3 19.388
344334 4 34.4334
Like that n no. of Amounts are there & n no. of decimal are there
How can i achieve the above Final_Result based on the Amount & Decimal columns.
Its very urgent......... reply pls. Give me the sol....
The following can be used to display the value with the required number of decimal places:
select to_char(10000/power(10,2), '999999999 90.'||rpad ('0',2,'0' ))
from mytable;
select to_char(10000/power(10,2),
from mytable;
Sorry, using your column names that should be:
select to_char(amount/power(10,de cimal),'99 999999990. '||rpad('0 ',decimal, '0'))
from mytable;
select to_char(amount/power(10,de
from mytable;
ASKER
In my table, if a Decimal value is 0, then the result is coming like this:
Ex: Amount:19 Decimal:0 Final_Result: 19.
so, how can i remove the extra dot also in case of decimal is zero.
Thnx
Vasu
Ex: Amount:19 Decimal:0 Final_Result: 19.
so, how can i remove the extra dot also in case of decimal is zero.
Thnx
Vasu
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
How about using decode in Milleniumaire's earlier solution to handle the case when decimal = 0 -
select decode(decimal,0,to_char(a mount), <Millenium's earlier solution>)
select decode(decimal,0,to_char(a
ASKER
Thank u very much,
Its helped me alot. For my big problem, its simply a simple & good solution.
Its helped me alot. For my big problem, its simply a simple & good solution.
ASKER
Thnx a lot Milleniumaire for your excellent solution.
Regards,
Vasu
Regards,
Vasu
select amount/power(10,decimal)
from mytable;
However, values such as 100.00 aren't normally displayed or stored with the .00 decimal places so if you need to display these decimals as well, the to_char function will need to be used.