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....

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.

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

0

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

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.

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

select to_char(amount/power(10,decimal),'99999999990'||substr('.',1,decimal)||rpad('0',decimal,'0'))
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.

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

Srinivasu_VAuthor Commented:

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

0

Srinivasu_VAuthor Commented:

Thnx a lot Milleniumaire for your excellent solution.

Regards,
Vasu

0

Featured Post

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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.