Re: count(*) with NULLs involved

Krishna K. Kurup (kkurup_at_sequent.com) wrote:
: 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.

You can also do

select count(*) from table
where nvl(column_X,'some_crap_value') not in ('B','C');

Joe

-- 
                                   
=-------------------------------------------------------------------------=
Joe Nardone <nardone_at_patriot.net>
  "There are many dying children out there whose last wish is to meet me."
                                                  - David Hasselhoff
  "The most beautiful thing we can experience is the mysterious.  It is
  the source of all true art and all science.  He to whom this emotion
  is a stranger, who can no longer pause to wonder and stand rapt in awe,
  is as good as dead: his eyes are closed."       - Einstein