Re: IN() question

From: Sergey V Dolgov <dsv_at_pptus.oilnet.ru>
Date: Sun, 05 May 2002 22:48:21 -0800
Message-ID: <F001.004587D3.20020505224821@fatcity.com>

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_at_pptus.oilnet.ru

--

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

Author: Sergey V Dolgov
  INET: dsv_at_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_at_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). Received on Mon May 06 2002 - 01:48:21 CDT