Oracle-L: RE: IN() question

From: kranti pushkarna <kranti_pushkarna_at_staarship.com>
Date: Mon, 06 May 2002 00:18:28 -0800
Message-ID: <F001.004588BA.20020506001828@fatcity.com>

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: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).
--

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

Author: kranti pushkarna
INET: kranti_pushkarna_at_staarship.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------