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

Since the only field that could have NULLs is the pcg_numlig ( given the NOT NULL constraint on the others) , eliminate that from your second count(*)... query, and the result should be the same as the first...

John.Greco_at_dot.state.mn.us

tkyte_at_us.oracle.com (Thomas Kyte) wrote:

>You must have NULLS in the table. Consider:
>
>SQL> select * from dual;
>
>D
>-
>X
>
>SQL> select * from dual where null = null;
>
>no rows selected
>
>SQL> select * from dual
> 2 where null = null
> 3 OR null <> null;
>
>no rows selected
>
>
>Null is NEVER equal to NULL
>Null is NEVER Not equal to NULL
>
>
>You need to say something like:
>
>where ( a = a OR a is null )
> and ( b = b OR b is null )
>....
>
>
>On 9 Jun 1997 13:03:35 GMT, "Eric L'HUILLIER" <elhuilli_at_ifremer.fr> wrote:
>
>>Hi Netters,
>>
>>I tried the following queries :
>>
>>SQL> select count(*) from plan_comptable;
>>
>> COUNT(*)
>>---------
>> 1784
>>

 [snip]
>>
>>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 think I should had the same result on both queries. Don't you think so ?
>>If not why.
>>

 [snip]
>>
>>eric.l.huillier_at_ifremer.fr
>>
>>IFREMER Brest (FRANCE)
>>
>>Tel : 02.98.22.47.40 (From France)
>>Tel : +(33) 2.98.22.47.40 (From rest of the world)
>
>
>Thomas Kyte
>tkyte_at_us.oracle.com
>Oracle Government
>Bethesda MD
>
>http://govt.us.oracle.com/ -- downloadable utilities
>
>----------------------------------------------------------------------------
>Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Tue Jun 10 1997 - 00:00:00 CDT