RE: Re: SQL help needed

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 <breitliw_at_centrexcc.com>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>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: sfaroult_at_oriolecorp.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).