Oracle  +  plsql

Posted on 2009-03-30
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;
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.


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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Workhours Count beetween Workhours 3 49
oracle query 3 34
Oracle perfomance issue. 4 48
Oracle Partitions. 1 14
I showed you how to use console view (HERE ( -– but how do you set it up on the admin side of Salesforce? Note that you have to have Admin leve…
Read about achieving the basic levels of HRIS security in the workplace.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

739 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