Try this SELECT COUNT(*) from leisure_plan_master_temp where membership_class = 'D' AND (PAY_METHOD IS NULL or pay_METHOD NOT IN ('C','P')) ;
-----Original Message----- Sent: Monday, May 06, 2002 12:18 PM To: Multiple recipients of list ORACLE-L Hello Lisa, Null values are excluded from search list for "in" statement. Saturday, May 04, 2002, 6:43:38 AM, you wrote: KL> Slap me if this is a dumb question. KL> Here's my pay methods SQL>> SELECT ASCII(PAY_METHOD), PAY_METHOD, COUNT(*) KL> 2 FROM LEISURE_PLAN_MASTER_TEMP KL> 3 WHERE MEMBERSHIP_CLASS = 'D' KL> 4 GROUP BY PAY_METHOD; KL> ASCII(PAY_METHOD) P COUNT(*) KL> ----------------- - ---------- KL> 67 C 42955 KL> 80 P 34373 KL> 11786 KL> I expected this statement to return the 11,786 records that have null KL> values. However, it doesn't: SQL>> SELECT COUNT(*) KL> 2 from leisure_plan_master_temp KL> 3 where membership_class = 'D' KL> 4 AND pay_METHOD NOT IN ('C','P'); KL> COUNT(*) KL> ---------- KL> 0 KL> But when I do this, I get the answer I expect. SQL>> SELECT COUNT(*) KL> 2 from leisure_plan_master_temp KL> 3 where membership_class = 'D' KL> 4 AND PAY_METHOD IS NULL; KL> COUNT(*) KL> ---------- KL> 11786 KL> This isn't exactly correct. There may be other values in this field, and if KL> they show up I need to include them, not just records where this field is KL> null. What am I missing? Is it because the value is NULL that Oracle KL> excludes it from the IN() statement, because of the classic definition of KL> NULL (can't be defined, therefore can't be sure it's not a C or a P)? KL> This is easy enough to fix, I'll change my data load to populate the null KL> values with my own code. But still? Have I got the WHY correct? KL> Thanks for any light someone can shed on this stupid question. KL> Lisa Koivu KL> Oracle Database Monkey Mama KL> Fairfield Resorts, Inc. KL> 5259 Coconut Creek Parkway KL> Ft. Lauderdale, FL, USA 33063 -- Best regards, Sergey mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sergey V Dolgov INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kranti pushkarna INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).