Re: SQL help needed

[Wolfgang Breitling]

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
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 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).