Home » SQL & PL/SQL » SQL & PL/SQL » Range value
Range value [message #185411] Tue, 01 August 2006 10:56 Go to next message
gopal2005
Messages: 9
Registered: October 2005
Location: Detroit
Junior Member
I am trying to find out how to get a value if a value lies between certain range.
Say suppose if the value is 5 from a database field then I should get the value '1-10' as the value lies between '1-10'.
If the value is 14 then the value lies between '11-20'
If the value is 25 then the value lies between '21-30'
If the value is 36 then the value lies between '31-40'
If the value is 49 then the value lies between '41-50'
and so on ...
Re: Range value [message #185412 is a reply to message #185411] Tue, 01 August 2006 11:00 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
So what is your question?
Also what does your post have to do with Oracle?
Re: Range value [message #185413 is a reply to message #185412] Tue, 01 August 2006 11:18 Go to previous messageGo to next message
gopal2005
Messages: 9
Registered: October 2005
Location: Detroit
Junior Member
OK may be I am not clear.
I am getting data from a table and that data is a number. I want to display the data if the value is in certain range.


select rownum from (select 1 from dual
group by cube(1,2,3,4,5,6,7)) where rownum <= 50

This will give data from 1 to 50.

Now what I need is to populate another field when the first row data is 1 then the data lies between range 1 to 10 and should insert the value as 1-10.
and if the 12th row data is 12, then that lies in the range of 11 to 20 and that value should be inserted as 11-20 and so on. The result should be like

ROWNUM range_value
1 1-10
2 1-10
3 1-10
4 1-10
5 1-10
6 1-10
7 1-10
8 1-10
9 1-10
10 1-10
11 11-20
12 11-20
13 11-20
14 11-20
15 11-20
16 11-20
17
18
19
20
21 21-30
22 21-30
23 21-30
24 21-30
25 21-30
26
27
28
29
30
31 31-40
32 31-40
33 31-40
34 31-40
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50


Hope this helps.
Re: Range value [message #185416 is a reply to message #185413] Tue, 01 August 2006 11:37 Go to previous messageGo to next message
gopal2005
Messages: 9
Registered: October 2005
Location: Detroit
Junior Member
I was able to solve the problem using CASE statement. Thank you all.
Re: Range value [message #185418 is a reply to message #185411] Tue, 01 August 2006 11:47 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
Another way, without using CASE:

select rownum,
       trunc((rownum-1)/10)*10+1||'-'||(trunc((rownum-1)/10)+1)*10 range_value
from (select 1 from dual
group by cube(1,2,3,4,5,6,7)) 
where rownum <= 50 


Re: Range value [message #185427 is a reply to message #185418] Tue, 01 August 2006 12:18 Go to previous message
gopal2005
Messages: 9
Registered: October 2005
Location: Detroit
Junior Member
Thank you for your response. Appreciate your help.
Previous Topic: creating table in procedure
Next Topic: Code Errors- Please assist
Goto Forum:
  


Current Time: Wed Dec 07 04:57:41 CST 2016

Total time taken to generate the page: 0.08878 seconds