Re: Strange SQL Results

From: Dave Wotton <Dave.Wotton_at_dwotton.nospam.clara.co.uk>
Date: Sun, 11 Apr 1999 06:17:56 GMT
Message-ID: <o8XP2.946$uU2.23157@mors.clara.net>

It's possible that you have corrupt values in DateC. Try simply doing a SELECT DATEC FROM ... If there are corrupt values, this statement will stop with an error when it hits one and tries to display it. I think it is possible that a SELECT COUNT(*) ... may simply stop and return the wrong count value, and not always return an error code.

Phillippa Verity Davies wrote in message <7emrjc$eg9$1_at_news8.svr.pol.co.uk>...
>I would like to understand why I am getting the following results, it seems
>like a possible problem in the database engine to me, but if some one can
>explain it great! It is happening on Oracle 8, though I have no idea of the
>fix level of the product.
>
>I have a large table with at least 3 date fields all of which allow nulls,
I
>am just counting the rows that match certain criteria.
>
> SELECT COUNT(*) WHERE DateA>TODATE('01-01-1998')
>returns approx. 20K
> SELECT COUNT(*) WHERE DateA>TODATE('01-01-1998') OR
>DateB>TODATE('01-01-1998')
>returns approx. 50K
> SELECT COUNT(*) WHERE DateA>TODATE('01-01-1998') OR
>DateB>TODATE('01-01-1998') OR DateC>TODATE('01-01-1998')
>Returns approx. 30K
>
>I am surprised that an additional OR condition could reduce the number of
>rows.

Dave.
--
Remove "nospam" from my address to reply by email Received on Sun Apr 11 1999 - 01:17:56 CDT