Re: Finding number withing specific range
From: Gerard H. Pille <ghp_at_skynet.be>
Date: Wed, 24 Apr 2013 22:29:45 +0200
Message-ID: <517840b9$0$3121$ba620e4c_at_news.skynet.be>
Pankaj wrote:
> Hi,
>
> I have a requirement where I need to check the lower and higher
> "range" for specific number
>
> Example:
>
> Range:
> 0.05
> 0.1
> 0.15
> 0.2
> 0.25
> 0.3
> 0.35
>
> So if I am provided with number say 0.29, I have to return below
> outout
>
> Number Low_range High_range
> 0.29 0.2 0.3
> 0.13 0.1 0.15
>
> How can I do this via oracle sql? I tried couple of options but
> nothing seems to work.
>
> Any help would be appreciated
>
Date: Wed, 24 Apr 2013 22:29:45 +0200
Message-ID: <517840b9$0$3121$ba620e4c_at_news.skynet.be>
Pankaj wrote:
> Hi,
>
> I have a requirement where I need to check the lower and higher
> "range" for specific number
>
> Example:
>
> Range:
> 0.05
> 0.1
> 0.15
> 0.2
> 0.25
> 0.3
> 0.35
>
> So if I am provided with number say 0.29, I have to return below
> outout
>
> Number Low_range High_range
> 0.29 0.2 0.3
> 0.13 0.1 0.15
>
> How can I do this via oracle sql? I tried couple of options but
> nothing seems to work.
>
> Any help would be appreciated
>
I can see that nothing works, even you fail!
Shouldn't the low range for 0,29 be 0,25 ???
In which case, for a given number N
select d.N, r1.range low_range, r2.range high_range
from
(select N from dual) d,
range_table r1,
range_table r2
where r1.range < d.N
and not exists (select range from range_table where range > r1.range and range < d.N)
and r2.range > d.N
and not exists (select range from range_table where range > d.N and range < r2.range)
Of course, this will not work if N does not fit into a range.
You could cover that with scalar selects.
select d.N,
(select range from range_table t1 where t1.range < d.N and not exists (select range from range_table t2 where t2.range > t1.range and t2.range < d.N)) low_range, (select range from range_table t1 where t1.range > d.N and not exists (select range from range_table t2 where t2.range < t1.range and t2.range > d.N)) high_range from
(select N from dual) d Received on Wed Apr 24 2013 - 22:29:45 CEST