How about :
select num
from NUMBERS,
( select begin, end from RANGE)
where num between begin and end;
Paul
-----Original Message-----
Sent: Friday, February 22, 2002 10:14 AM
To: Multiple recipients of list ORACLE-L
You are right that the range aren't necessarily contigous.
I'd probably have to write it in PL/SQL, I just want to see
if one can do this with SQL.
Thanks.
>From: [EMAIL PROTECTED]
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: Re: sql question
>Date: Thu, 21 Feb 2002 21:33:20 -0800
>
>Rich,
>
>Are you sure that that is what you want ?
>Suppose your range values were something like :
> begin end
> 1 9
> 15 19
> 23 29
>ie, the RANGE table shows that 10-14 and 20-22 are invalid ("not allowed")
>values.
>
>Your problem statement and the SQL that Paul provides for the problem
>statement
>would return numbers like 10, 11, 20,21 which are, actually, invalid.
>
>You'd have to write a cursor to loop through the valid ranges ??
>
>Hemant K Chitale
>Principal DBA
>Chartered Semiconductor Manufacturing Ltd
>
>
>Paul Baumgartel <[EMAIL PROTECTED]> 22/02/2002 12:43 PM
>Sent by: [EMAIL PROTECTED]
>
>Please respond to ORACLE-L
>
> To: Multiple recipients of list ORACLE-L
><[EMAIL PROTECTED]>
> cc: (bcc: CHITALE Hemant Krishnarao/IT/CHRT/ST Group)
> Subject: Re: sql question
>
>
>
>
>
>
>
>
>To use your example column names:
>
>select num from numbers where num between
>(select min(begin) from range) and (select max(end) from range);
>
>
>--- oracle dba <[EMAIL PROTECTED]> wrote:
> > Hi all,
> >
> > I have a SQL question. Suppose I have a table called RANGE looks
> > like
> > this:
> >
> > begin end
> > 1 9
> > 10 19
> > 20 29
> >
> > Then I have a table NUMBERS that's full of bunch of numbers like
> > this:
> >
> > num
> > 1
> > 2
> > 3
> > 4
> > ...
> > 98
> > 99
> > 100
> >
> > I want to write a SQL that returns the number that are within
> > the ranges defined in the RANGE table. So number 1 through 29
> > should be returned.
> >
> > Can someone help me with this? Thanks.
> >
> > Rich
> >
> > _________________________________________________________________
> > Send and receive Hotmail on your mobile device: http://mobile.msn.com
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: oracle dba
> > INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing
> > Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
>
>
>__________________________________________________
>Do You Yahoo!?
>Yahoo! Sports - Coverage of the 2002 Olympic Games
>http://sports.yahoo.com
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Paul Baumgartel
> INET: [EMAIL PROTECTED]
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
>
>
>
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author:
> INET: [EMAIL PROTECTED]
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
_________________________________________________________________
Send and receive Hotmail on your mobile device: http://mobile.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: oracle dba
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
