Re: SQL help needed

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Tue, 16 Sep 2003 20:04:37 -0800
Message-ID: <F001.005D0159.20030916200437@fatcity.com>

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: breitliw_at_centrexcc.com

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: ListGuru_at_fatcity.com (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).

Received on Tue Sep 16 2003 - 23:04:37 CDT