• Status: Solved
• Priority: Medium
• Security: Public
• Views: 528

# 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....
0
Srinivasu_V
• 4
• 3
1 Solution

Commented:
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.
0

Commented:
The following can be used to display the value with the required number of decimal places:

from mytable;
0

Commented:
Sorry, using your column names that should be:

from mytable;
0

Author Commented:
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
0

Commented:
The statement is starting to look a little clumsy, but try this as it appears to work:

from mytable;

The 2nd argument of the to_char function is used to supply the format mask, which determines how many decimal places to display.  In the above statement this format mask is constructed using:

The '99999999990' literal can be as large has you like to handle the largest number stored in your table i.e. just add extra or fewer 9's as required.  The substr simply adds a decimal point when the value of decimal > 0.  A decode or case could also be used, but the substr is shorter.  The rpad adds the required number of decimal places to the format mask.
0

Commented:
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>)
0

Author Commented:
Thank u very much,
Its helped me alot. For my big problem, its simply a simple & good solution.
0

Author Commented:
Thnx a lot Milleniumaire for your excellent solution.

Regards,
Vasu
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.