Thanks Paul,
That worked.
>From: [EMAIL PROTECTED]
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: RE: sql question
>Date: Fri, 22 Feb 2002 07:53:39 -0800
>
>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).
_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.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).
