Re: SQL Problem w/MINUS

there are plenty of bugs in 8.1.x. another site found where sum(x) doesn't add properly! and on linux, things could be worse! i suggest avoiding 8.1.x releases for another 12 months.

as for the minus problem...i suppose the 'm' is for million rather than 1,000s, in which case you may be using more resources than what are available. i suggest you consider using pl/sql. it may be slower, but it can control resource consumption much better. or you could try a subquery, but you may run into similar problems.

select cust_nbr
from customer
where

    age >= 30
and

    cust_nbr in

     (select cust_nbr
      from order
      where amount < 10
     )

"Steve Fuller" <sfulle_at_alltel.net> wrote in message news:8qohs8$cf$1_at_iac5.navix.net...
> Hi All,
>
>
> I am using Oracle 8.1.6 on a Linux platform and I am finding unwanted
> results from a SQL statement that uses the
> MINUS clause.
>
> select cust_nbr from customer where age >= 30
> minus
> select cust_nbr from order where amount >= 10
>
>
> The customer table has about 14m rows and the order table has 60m rows.
> I am trying to select all customers that have a single purchase of 10 or
> more.
> At the end I run reports on the result set and I get customers that are
 less
> than 30
> Questions
> <1> Is there a better way write this select?
> <2> Are there any problems with using the MINUS clause
> <3> Are there any bugs related to SQL within Oracle for Linux
>
> Thanks
>
> Steve Fuller
>
>
Received on Mon Sep 25 2000 - 22:20:33 CDT