Re: Finding number withing specific range
From: ddf <oratune_at_msn.com>
Date: Thu, 25 Apr 2013 05:54:28 -0700 (PDT)
Message-ID: <e3947077-fc14-405e-8db7-ba05637b34b4_at_googlegroups.com>
On Wednesday, April 24, 2013 2:29:45 PM UTC-6, Gerard H. Pille wrote:
> 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
Date: Thu, 25 Apr 2013 05:54:28 -0700 (PDT)
Message-ID: <e3947077-fc14-405e-8db7-ba05637b34b4_at_googlegroups.com>
On Wednesday, April 24, 2013 2:29:45 PM UTC-6, Gerard H. Pille wrote:
> 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
And this is 'answered' in another group (comp.databases.oracle.server) in a similar fashion. I suppose he's looking for someone to actually do his work rather than do it himself.
David Fitzjarrell Received on Thu Apr 25 2013 - 14:54:28 CEST