RE: where clause

From: FOX, Simon <Simon.FOX_at_crewe.sema.slb.com>
Date: Mon, 09 Feb 2004 10:13:48 +0000
Message-id: <635D4DEEFA0CD311ADA1009027469FE10311E9AE@notes6.sema.co.uk>

I've found that telling myself that null fails all tests (except is null) works.

Simon Fox

CRH 0161 601 8723

-----Original Message-----

From: David Boyd [mailto:davidb158_at_hotmail.com] Sent: 06 February 2004 19:55
To: oracle-l_at_freelists.org
Subject: where clause

Hi List,

I found following results that I don't understand.

SQL> desc test

Name                                      Null?    Type


----------------------------------------- -------- -----------------

STATUS_FLAG                                        CHAR(1)

SQL> SELECT COUNT(*) FROM test;

COUNT(*)

1681

SQL> SELECT COUNT(*) FROM test WHERE status_flag IS NULL;

COUNT(*)

1638

SQL> SELECT COUNT(*) FROM test WHERE status_flag ='A';

COUNT(*)

SQL> SELECT COUNT(*) FROM test WHERE status_flag ='S';

COUNT(*)

SQL> SELECT COUNT(*) FROM test WHERE status_flag !='A';

COUNT(*)

I thought the last query should return 1676 (status_flag ='S' + status_flag is null) instead of 38 (status_flag ='S' only). Any ideas???

Let the advanced features & services of MSN Internet Software maximize your online time. http://click.atdmt.com/AVE/go/onm00200363ave/direct/01/

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

This e-mail and the documents attached are confidential and intended solely for the addressee; it may also be privileged. If you receive this e-mail in error, please notify the sender immediately and destroy it. As its integrity cannot be secured on the Internet, the Atos Origin group liability cannot be triggered for the message content. Although the sender endeavours to maintain a computer virus-free network, the sender does not warrant that this transmission is virus-free and will not be liable for any damages resulting from any virus transmitted.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

Received on Mon Feb 09 2004 - 04:13:48 CST