Home » SQL & PL/SQL » SQL & PL/SQL » Help with shortening code
Help with shortening code [message #210403] Wed, 20 December 2006 11:47 Go to next message
Anto318
Messages: 27
Registered: December 2006
Junior Member
Hi all,

I have a script to round a figure to the nearest multiple of a supplied number

e.g.
If 3 is entered the result is 5
If 9 is entered the result is 10
If 12 is entered the result is 10

The code I have is too long to import into my database so to be able to import it needs to be shorter

Can anybody help me shorten the code?

The code is:

select case when mod(to_number(aavalue.valuestring),5) = 0 THEN to_number(aavalue.valuestring) when mod(to_number(aavalue.valuestring),5) < 3 THEN to_number(aavalue.valuestring) - mod(to_number(aavalue.valuestring),5) Else to_number(aavalue.valuestring)+( 5- mod(to_number(aavalue.valuestring),5) ) End  from


I hope ye can help.
Thank you in advance,
AM
Re: Help with shortening code [message #210404 is a reply to message #210403] Wed, 20 December 2006 12:02 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I don't know what you mean by it's too long to import. That makes no sense to me.
If you want code for the exact scenario you provide, disregarding other values, then this will work:
decode(aavalue.valuestring,3,5,10)
Re: Help with shortening code [message #210446 is a reply to message #210404] Wed, 20 December 2006 19:29 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
accept n prompt "Number: "
accept m prompt "Multiple: "

select round(&n/&m) * &m
from dual
/



Ross Leishman
Re: Help with shortening code [message #210501 is a reply to message #210446] Thu, 21 December 2006 02:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could try this:
select val,(val+2.5)  - mod(val+2.5,5) from
(select level val from dual connect by level <= 30);
It effectively rounds to the nearest multiple of 5.

I can't help noticing that you've posted several questions, all concerning odd ways of rounding numbers - what is it that you're doing with all these?
Re: Help with shortening code [message #210503 is a reply to message #210403] Thu, 21 December 2006 02:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you want to shorten the CASE statement, (I'm with @Joy_Division in not understanding what you mean when you say it's too long to import) you can just get rid of the clause
when mod(to_number(aavalue.valuestring),5) = 0 THEN to_number(aavalue.valuestring)
entirely, as it is entirely contained within the functionality of the clause
when mod(to_number(aavalue.valuestring),5) < 3 THEN to_number(aavalue.valuestring) - mod(to_number(aavalue.valuestring),5)
Re: Help with shortening code [message #210508 is a reply to message #210403] Thu, 21 December 2006 02:42 Go to previous messageGo to next message
Anto318
Messages: 27
Registered: December 2006
Junior Member
I honestly don't know why they are all needed just got 18 script requirements and they are on it, I do not know why but they have to be done.

I got it sorted anyway guys thanks very much for your help
Re: Help with shortening code [message #210592 is a reply to message #210508] Thu, 21 December 2006 07:56 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Whoops, silly me. I didn't read your full question. I only saw the three examples, so my solution was pretty inept. I was thinking, "Wow, Ross and Jrow are great pattern solvers to come up with such unusual solutions" until I looked back and saw the full question.

I've had a lot of these kind of days recently.
Previous Topic: Primary Key Column
Next Topic: Need cyrillic output with commandline tool sqlplus on linux
Goto Forum:
  


Current Time: Sun Dec 04 10:49:39 CST 2016

Total time taken to generate the page: 0.11521 seconds