I was thinking along the same lines minus the outer query which is totally unnecessary, but it is the beginning of my day here :-).
SF >----- ------- Original Message ------- ----- >From: Wolfgang Breitling <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Tue, 16 Sep 2003 20:04:37 > >How about > >select linenbr from (select rownum+&&min as linenbr >from dba_objects where >rownum < (&&max-&&min) >minus select linenbr from kirti where linenbr > >&&min and linenbr < &&max) >/ > >pollux.stats.scott> select * from kirti; > > EMPNBR LINENBR >---------- ---------- > 100 1 > 200 4 > 300 8 > 400 9 > 500 14 > >5 rows selected. > >pollux.stats.scott> select linenbr from (select >rownum+&&min as linenbr >from dba_objects where rownum < (&&max-&&min) > 2 minus select linenbr from kirti where linenbr >> &&min and linenbr < >&&max) > 3 / >Enter value for min: 1 >Enter value for max: 10 > > LINENBR >---------- > 2 > 3 > 5 > 6 > 7 > >5 rows selected. > >pollux.stats.scott> undef min >pollux.stats.scott> undef max >pollux.stats.scott> select linenbr from (select >rownum+&&min as linenbr >from dba_objects where rownum < (&&max-&&min) > 2 minus select linenbr from kirti where linenbr >> &&min and linenbr < >&&max) > 3 / >Enter value for min: 4 >Enter value for max: 14 > > LINENBR >---------- > 5 > 6 > 7 > 10 > 11 > 12 > 13 > >7 rows selected. > >pollux.stats.scott> undef min >pollux.stats.scott> undef max >pollux.stats.scott> select linenbr from (select >rownum+&&min as linenbr >from dba_objects where rownum < (&&max-&&min) > 2 minus select linenbr from kirti where linenbr >> &&min and linenbr < >&&max) > 3 / >Enter value for min: 4 >Enter value for max: 10 > > LINENBR >---------- > 5 > 6 > 7 > >3 rows selected. > >At 07:29 PM 9/16/2003 -0800, you wrote: >>Hello Listers, >> >>I need some SQL help. >>Not sure if this is possible in plain SQL or not, >but here it is: >>I have a table with two columns: >>EMPNBR LINENBR >>------ ------- >> 100 1 >> 200 4 >> 300 8 >> 400 9 >> 500 14 >> >>How would I find out the missing linenbrs >(2,3,5,6,7) if I was given >>minlinenbr as 1 and >>maxlinenbr as 10 that would be passed as variables >(using &). >> >>Thanks. >> >>- Kirti > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).
