Re: Finding number withing specific range
From: ddf <oratune_at_msn.com>
Date: Wed, 24 Apr 2013 08:42:33 -0700 (PDT)
Message-ID: <e4fbb6a9-6886-495e-8b04-a89f1a4049ce_at_googlegroups.com>
On Tuesday, April 23, 2013 4:35:59 PM UTC-6, Charles Hooper wrote:
> On Tuesday, April 23, 2013 5:31:18 PM UTC-4, Pankaj wrote:
>
> > 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
>
>
>
> Pankaj,
>
>
>
> It is generally best to show what you have already tried when posting a request for help. If I am understanding your problem correctly, there is an error in your sample output; the 0.2 should be 0.25.
>
>
>
> There are a couple of ways to solve the problem. One method is to use the LEAD analytic function to allow the LOW_RANGE and HIGH_RANGE (the value returned by LEAD) values to be returned on the same row. Once that is done, the output may be slid into an inline view and then you can test to determine if the specific number to be checked (0.29, 0.13) is BETWEEN a specific LOW_RANGE and HIGH_RANGE pair in the inline view.
>
>
>
> See:
>
> http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions074.htm
>
>
>
> Charles Hooper
>
> IT Manager/Oracle DBA
>
> K&M Machine-Fabricating, Inc.
Date: Wed, 24 Apr 2013 08:42:33 -0700 (PDT)
Message-ID: <e4fbb6a9-6886-495e-8b04-a89f1a4049ce_at_googlegroups.com>
On Tuesday, April 23, 2013 4:35:59 PM UTC-6, Charles Hooper wrote:
> On Tuesday, April 23, 2013 5:31:18 PM UTC-4, Pankaj wrote:
>
> > 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
>
>
>
> Pankaj,
>
>
>
> It is generally best to show what you have already tried when posting a request for help. If I am understanding your problem correctly, there is an error in your sample output; the 0.2 should be 0.25.
>
>
>
> There are a couple of ways to solve the problem. One method is to use the LEAD analytic function to allow the LOW_RANGE and HIGH_RANGE (the value returned by LEAD) values to be returned on the same row. Once that is done, the output may be slid into an inline view and then you can test to determine if the specific number to be checked (0.29, 0.13) is BETWEEN a specific LOW_RANGE and HIGH_RANGE pair in the inline view.
>
>
>
> See:
>
> http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions074.htm
>
>
>
> Charles Hooper
>
> IT Manager/Oracle DBA
>
> K&M Machine-Fabricating, Inc.
There is an issue with a BETWEEN/lead solution when the submitted value matches an upper bound (which then becomes the next row's lower bound) as two rows are returned. It may be best to code a greater than or equal two on the lower range and a less than on the upper range to return the one 'proper' row. I won't post my example as that would provide the solution to the OP; know that BETWEEN will return two rows for a boundary value match.
David Fitzjarrell Received on Wed Apr 24 2013 - 17:42:33 CEST