[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 517
  • Last Modified:

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
Asked:
Srinivasu_V
  • 4
  • 3
1 Solution
 
MilleniumaireCommented:
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
 
MilleniumaireCommented:
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;
0
 
MilleniumaireCommented:
Sorry, using your column names that should be:

select to_char(amount/power(10,decimal),'99999999990.'||rpad('0',decimal,'0'))
from mytable;
0
Veeam and MySQL: How to Perform Backup & Recovery

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.

 
Srinivasu_VAuthor 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
 
MilleniumaireCommented:
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:

'99999999990'||substr('.',1,decimal)||rpad('0',decimal,'0'))

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
 
awking00Commented:
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

Keep up with what's happening at Experts Exchange!

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

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now