Re: SQL Problem w/MINUS

Your SAQL does not seem to match your requirement. The SQL

    selects all customer numbers for customers over 30     then
    eliminates any of those who have made any purchases     at all costing more than 10.

So you get all IDs for customers who have never made a purchase over 10.

You textual description could also be mis-interpreted. Do you want:

    over-30s who have made at least one purchase over 10     over-30s who have made exactly one purchase over 10 and in both cases, do you want to include, or exclude customers who have also made purchases under 10 ?

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Steve Fuller wrote in message <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

>

>