Link to home
Start Free TrialLog in
Avatar of sri rao
sri raoFlag for India

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....
Avatar of Milleniumaire
Milleniumaire
Flag of United Kingdom of Great Britain and Northern Ireland image

The following will give you the value you are wanting:

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.
The following can be used to display the value with the required number of decimal places:

select to_char(10000/power(10,2),'99999999990.'||rpad('0',2,'0'))
from mytable;
Sorry, using your column names that should be:

select to_char(amount/power(10,decimal),'99999999990.'||rpad('0',decimal,'0'))
from mytable;
Avatar of sri rao

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
ASKER CERTIFIED SOLUTION
Avatar of Milleniumaire
Milleniumaire
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of awking00
How about using decode in Milleniumaire's earlier solution to handle the case when decimal = 0 -
select decode(decimal,0,to_char(amount), <Millenium's earlier solution>)
Avatar of sri rao

ASKER

Thank u very much,
Its helped me alot. For my big problem, its simply a simple & good solution.
Avatar of sri rao

ASKER

Thnx a lot Milleniumaire for your excellent solution.

Regards,
Vasu