[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


Oracle  +  plsql

Posted on 2009-03-30
Medium Priority
Last Modified: 2013-12-19
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....
Question by:Srinivasu_V
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 16

Expert Comment

ID: 24017025
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.
LVL 16

Expert Comment

ID: 24017035
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;
LVL 16

Expert Comment

ID: 24017042
Sorry, using your column names that should be:

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

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Author Comment

ID: 24017960
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.

LVL 16

Accepted Solution

Milleniumaire earned 2000 total points
ID: 24018303
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.
LVL 32

Expert Comment

ID: 24019241
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>)

Author Closing Comment

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

Author Comment

ID: 24036511
Thnx a lot Milleniumaire for your excellent solution.


Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: You must have administrative privileges in order to create/edit Roles. Salesforce.com (http://www.salesforce.com/) (SFDC) is a cloud-based customer relationship management (CRM) system. It is a database most commonly used by sales and markeā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question