Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Queries give impossible results
>
> 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
>
You're right but in this case your logic is not applicaple because there is a NOT NULL constraint:
SQL> select count(*) from ci_customer where customer_type IS NULL;
COUNT(*)
0