Re: Queries give impossible results

"Jake S" <nospam_at_nospam.net> wrote in message news:jtLA9.151$ZR5.5062_at_read2.inet.fi...
> 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
>
>

I think you might receive that kind of result if the customer_type column contains a null value because testing customer_type <> 'PERSON' is not the correct way of doing it (in Oracle's SQL) if the field can contain a NULL value. Moreover is the combination of the two logical expressions that is misleading you in this case. I believe that what you wrote is legitimate on AS400's SQL dialect where nulls do not exist, but not in Oracle's SQL. Unfortunately at the moment of writing I can't test this, but what if you replace the latter expression with ... AND (customer_type IS NULL OR customer_type <> 'PERSON') ?

Bye,
Flavio Received on Thu Nov 14 2002 - 07:37:59 CST