Re: Strange SQL Results

Hi Phillippa,

This is because SQL, at least the SQL in Oracle has three valued logic. Any test returns three possible values: true, false or null. I don't know better than that any NULL overrides ANY results. Eg if a concatenate in a string three columns and ANY of these three columns is NULL the result will be NULL.
The same applies probably here. As columns can be NULL, if any of those columns is NULL, the record will not be considered. The obvious solution is
 SELECT COUNT(*) from table
WHERE
      nvl(DateA,to_date('01-01-0000')) > TO_DATE('01-01-1998') OR nvl(DateB,to_date('01-01-0000')) > TO_DATE('01-01-1998')  OR nvl(DateB,to_date('01-01-0000')) > TO_DATE('01-01-1998')

if you don't want the NULL records in.
This is not a bug in the engine, this is IMO by design. BTW you should be able to do a
select * from v$version
in sqlplus to get the correction version, release information.

Hth,

Sybrand Bakker, Oracle DBA

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.
>
>
Received on Sat Apr 10 1999 - 03:30:16 CDT