Re: Weird "select count(*)..."

Eric L'HUILLIER <elhuilli_at_ifremer.fr> described two counts that he believed should show equivalent results:

>
> SQL> select count(*) from plan_comptable;
>
> COUNT(*)
> ---------
> 1784

(table description snipped)

> SQL> select count(*) from plan_comptable
> 2 where pcg_numcptgen = pcg_numcptgen
> 3 and pcg_libcptgen = pcg_libcptgen
> 4 and pcg_typsolde = pcg_typsolde
> 5 and pcg_duramort = pcg_duramort
> 6 and pcg_detail = pcg_detail
> 7 and pcg_numlig = pcg_numlig;
>
> COUNT(*)
> ---------
> 0

I bet that 'select count(*) from plan_comptable where pcg_numlig is null' will yield a count of 1784.

Why?

Since the pcg_numlig field is not 'NOT NULL' (that is, null values are allowed), my guess is that every row has a null value in this field. In line 7 you specify that 'pcg_numlig = pcg_numlig'. This test will fail for every row for which pcg_numlig is null. According to Oracle, you can not compare null values in this way.

Change line 7 to:

   and ((pcg_numlig is null) or (pcg_numlig = pcg_numlig));

That should work, and I hope this helps.

Later,
Lance Humpert
Thomas Jefferson University
Philadephia, Pa.
Lance.Humpert_at_mail.tju.edu Received on Wed Jun 11 1997 - 00:00:00 CDT