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) Sat, 20 March 2010 06:42
 chester04 Messages: 3Registered: 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.

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
 cookiemonster Messages: 12742Registered: 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
 Frank Messages: 7880Registered: 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
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
 Kevin Meade Messages: 2101Registered: 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
 Frank Messages: 7880Registered: 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
 Michel Cadot Messages: 64845Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 Kevin Meade Messages: 2101Registered: 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
 chester04 Messages: 3Registered: 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
 Michel Cadot Messages: 64845Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Does this mean you found a solution?

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
 chester04 Messages: 3Registered: 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
 Michel Cadot Messages: 64845Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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: Mon May 29 00:55:21 CDT 2017

Total time taken to generate the page: 0.09082 seconds