Solved

Oracle  +  plsql

Posted on 2009-03-30
8
457 Views
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....
0
Comment
Question by:Srinivasu_V
  • 4
  • 3
8 Comments
 
LVL 16

Expert Comment

by:Milleniumaire
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.
0
 
LVL 16

Expert Comment

by:Milleniumaire
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;
0
 
LVL 16

Expert Comment

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

select to_char(amount/power(10,decimal),'99999999990.'||rpad('0',decimal,'0'))
from mytable;
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:Srinivasu_V
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.

Thnx
Vasu
0
 
LVL 16

Accepted Solution

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

'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
 
LVL 32

Expert Comment

by:awking00
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>)
0
 

Author Closing Comment

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

Author Comment

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

Regards,
Vasu
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Salesforce.com’s Console is a great tool to view activities, leads, contacts, accounts and opportunities all in one screen. It is particularly effective during call blocks and working numerous activities at a time in a quick, repetitive fashion (suc…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

777 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