Re: Queries give impossible results

I can think of a couple of ways to MAKE this happen in a live demo - but I'm assuming you're not trying to wind people up.

So would you post a full execution path for each of the four queries - (not just the autotrace thing) and check that the execution plan Oracle reports is the one it actually uses.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA__________November 7/9   (Detroit)
____USA__________November 19/21 (Dallas)
____England______November 12/14

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





Jake S wrote in message ...


>I'm having strange problems with Oracle (8.1.7). Queries give results

that


>are not possible. I added a simple example below. First two count

values are


>correct, third is not possible, as you can see. Have you ever seen

anything


>like this?

>

>I've discussed this with several colleagues but this is something new

for


>them also. This has been hard to believe for them also, so they have

tried


>these by themselves. Some queries give correct results but some

doesn't.


>I've used sqlplus, Oracle Enterprice Manager and Toad so the software

is not


>the problem.

>

>- Jake -

>

>SQL> select count(*) from ci_customer;

>

>  COUNT(*)

>----------

>    138295

>

>SQL> SELECT count(*) FROM ci_customer WHERE customer_state =

'ACTIVE';


>

>  COUNT(*)

>----------

>    135324

>

>SQL> SELECT count(*) FROM ci_customer WHERE customer_type <>

'PERSON';


>

>  COUNT(*)

>----------

>    138292

>

>SQL> SELECT count(*) FROM ci_customer  WHERE customer_state =

'ACTIVE' AND


>customer_type <> 'PERSON';

>

>  COUNT(*)

>----------

>         4

>

>

>