Return-Path: Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by naude.co.za (8.11.2/8.11.2) with SMTP id g466ZbW09702 for ; Mon, 6 May 2002 02:35:37 -0400 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id XAA92108; Sun, 5 May 2002 23:43:56 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b71/bab) via UUCP id 004587D3; Sun, 05 May 2002 22:48:21 -0800 Message-ID: Date: Sun, 05 May 2002 22:48:21 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: Sergey V Dolgov Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: Sergey V Dolgov Subject: Re: IN() question Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 71; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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:dsv@pptus.oilnet.ru -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sergey V Dolgov INET: dsv@pptus.oilnet.ru 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: ListGuru@fatcity.com (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).