Home » SQL & PL/SQL » SQL & PL/SQL » Function that multiple the number when there is more than 3 decimal point (mered by CM) (PL/SQl Developer, XP)
Function that multiple the number when there is more than 3 decimal point (mered by CM) [message #448186] Sat, 20 March 2010 06:42 Go to next message
chester04
Messages: 3
Registered: March 2010
Location: Phil
Junior Member
Hi All,

Can anyone teach me how to create a function that will multiply the number when the number has more than 3 decimal point.

Example.

123.012 - multiply by 10
123.0123 - multiply by 100
123.01234 - miltiply by 1000

Column A is the result of the number being multiplied according to decimal point.

Column B contains the multiplier used in column A.

Hope somebody will help me.

Thanks in advance.
Re: Function that multiple the number when there is more than 3 decimal point (mered by CM) [message #448196 is a reply to message #448186] Sat, 20 March 2010 07:12 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use the round function to check if the value passed in has more than 3 decimal places.
Re: Function that multiple the number when there is more than 3 decimal point (mered by CM) [message #448197 is a reply to message #448186] Sat, 20 March 2010 07:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You do know that 123.012 = 123.01200, right?
So either this is a homework exercise, in which case you should at least show us some effort you put into it, or the requirements don't make sense, or the original value in the column represents something non-numerical.
anyway, a cheap trick would be to convert to string, remove the decimal separator, insert it again at the position before the penultimate character and reconvert back to number.


but that is so dirty that it won't get you very high grades Smile
Re: Function that multiple the number when there is more than 3 decimal point (mered by CM) [message #448201 is a reply to message #448186] Sat, 20 March 2010 09:35 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
as was suggested, you need to do some basic string manipulation

1) count the number of significant digits after the decimal point. Remember that converting to a string will remove insignificant digits.

2) once you know this, subtract 3 and if greater than zero, user power(10,...) to multiply.

you will need to use the following functions in your solution:

instr()
substr()
length()
power()
case end;

Kevin
Re: Function that multiple the number when there is more than 3 decimal point (mered by CM) [message #448206 is a reply to message #448201] Sat, 20 March 2010 10:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you take the direction of string manipulation, you don't need to multiply, as I showed: just insert a "decimal point" at the correct location (i.e. always before the penultimate character, if instr(<original>, '.', -1) indicated that the original had at least 2 digits after the decimal separator)
Re: Function that multiple the number when there is more than 3 decimal point (mered by CM) [message #448207 is a reply to message #448201] Sat, 20 March 2010 10:22 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'd say you only need CASE (or DECODE) and TRUNC (in addition to multiplication).

Regards
Michel

[Updated on: Sat, 20 March 2010 12:01]

Report message to a moderator

Re: Function that multiple the number when there is more than 3 decimal point (mered by CM) [message #448210 is a reply to message #448186] Sat, 20 March 2010 10:49 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Yeah OK, so there are several ways to achieve it.
Re: Function that multiple the number when there is more than 3 decimal point (mered by CM) [message #448216 is a reply to message #448186] Sun, 21 March 2010 00:03 Go to previous messageGo to next message
chester04
Messages: 3
Registered: March 2010
Location: Phil
Junior Member
thanks everyone
Re: Function that multiple the number when there is more than 3 decimal point (mered by CM) [message #448218 is a reply to message #448216] Sun, 21 March 2010 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Does this mean you found a solution?
If so, please post it.

Regards
Michel
Re: Function that multiple the number when there is more than 3 decimal point (mered by CM) [message #448350 is a reply to message #448186] Mon, 22 March 2010 19:36 Go to previous messageGo to next message
chester04
Messages: 3
Registered: March 2010
Location: Phil
Junior Member
I just created a procedure and used a modulo or power function to calculate the decimal point.
Re: Function that multiple the number when there is more than 3 decimal point (mered by CM) [message #448361 is a reply to message #448350] Tue, 23 March 2010 00:33 Go to previous message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Well, post the code and we will be able to:
1/ improve it
2/ show you other ways


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand

[Updated on: Tue, 23 March 2010 00:43]

Report message to a moderator

Previous Topic: Problem with loop (yes, I searched the forum)
Next Topic: procedure taking time
Goto Forum:
  


Current Time: Sat Oct 01 04:10:02 CDT 2016

Total time taken to generate the page: 0.10960 seconds