Re: count(*) with NULLs involved

Charlie Crissman wrote:

> stuff deleted ......
 

> Oracle is not counting the
> nulls in my query.
>
> My query is as follows:
>
> SQL> select count(*) from test_table
> 2> where column_X not in ('B','C');
>
> I don't want to have to replace the NULL's in this column with ' ',

This is because you cannot equate null with '=' or '!=' operators and that's what the 'IN' operator is doing. The IS [NOT] NULL predicate returns only TRUE or FALSE (sql-92 has added UNKNOWN too).

One way to counter this would be comparison with a concatenated column. The query would look like:

	select count(*) from test_table
	where column_X||'XXXX' not in ('BXXXX','CXXXX');

Note: Make sure that the string you concatenate with is weird enough, not to appear in the resultant values. You get the idea.

thanks
Krishna Received on Fri Nov 01 1996 - 00:00:00 CET