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).