Re: Queries give impossible results

Maybe by now you have figured out what is going with your data and that ORACLE, after all, did not give you wrong results. I don't think ORACLE is capable of giving wrong results for simple queries like these.

> Using common sense it's easy to understand that there are more
> than 4 customers that are not 'PERSON' but are 'ACTIVE'.

I failed to see how this is common sense. Did I misunderstand you or did you come to this conclusion :

           count( customer_state = 'ACTIVE and customer_type <> 'PERSON')
   must be much larger than 4 because both these counts are so high

           count(customer_state = 'ACTIVE) : 135324
           count(customer_type <> 'PERSON') : 138292

(123450000444555666667890123456780xxxx)                                 

(xxxxabcdefghijklmmmmmmmnnnoppppptyuuuuu)

The two groups above can each have many, many members. But there may be only a few members (the xxxx) that belong to both groups ( the "and" operator in your case).

You can even try a count on the "or"

         count( customer_state = 'ACTIVE' or customer_type <> 'PERSON')
The result should be 135324 + 138292 - 4. This will be true regardless whether or not there are NULLs in the table.

• Jusung Yang