Re[2]: IN() question

From: Sergey V Dolgov <dsv_at_pptus.oilnet.ru>
Date: Mon, 06 May 2002 19:58:24 -0800
Message-ID: <F001.004598DE.20020506195824@fatcity.com>

Hello Roy,

You can include null in an IN() but it will have no effect.

Monday, May 06, 2002, 11:53:30 PM, you wrote:

PRE> Does this mean that you can't include Null as a value in an IN() clause? PRE> That is, would something like:

>> SQL> SELECT COUNT(*)
>> 2 from leisure_plan_master_temp
>> 3 where membership_class = 'D'
>> 4 AND pay_METHOD IN (Null, 'C','P');

PRE> count rows where pay_METHOD is Null? (Disregarding for the moment the PRE> problem Lisa was actually trying to solve.)

PRE> Thanks!

PRE> -Roy

PRE> Roy Pardee
PRE> Programmer/Analyst
PRE> SWFPAC Lockheed Martin IT
PRE> Extension 8487

PRE> -----Original Message-----

PRE> Sent: Friday, May 03, 2002 5:33 PM
PRE> To: Multiple recipients of list ORACLE-L

PRE> In any equivalence operation (which includes non-equivalence, too), NULL
PRE> never returns TRUE -- it just returns NULL which non-TRUE has the same
PRE> result as FALSE.  Only IS and IS NOT operators can be used to evaluate
PRE> NULLs...

PRE> ----- Original Message -----

PRE> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> PRE> Sent: Friday, May 03, 2002 5:43 PM

>> Slap me if this is a dumb question.
>>
>> Here's my pay methods
>>
>> SQL> SELECT ASCII(PAY_METHOD), PAY_METHOD, COUNT(*)
>> 2 FROM LEISURE_PLAN_MASTER_TEMP
>> 3 WHERE MEMBERSHIP_CLASS = 'D'
>> 4 GROUP BY PAY_METHOD;
>>
>> ASCII(PAY_METHOD) P COUNT(*)
>> ----------------- - ----------
>> 67 C 42955
>> 80 P 34373
>> 11786
>>
>> I expected this statement to return the 11,786 records that have null
>> values. However, it doesn't:
>>
>> SQL> SELECT COUNT(*)
>> 2 from leisure_plan_master_temp
>> 3 where membership_class = 'D'
>> 4 AND pay_METHOD NOT IN ('C','P');
>>
>> COUNT(*)
>> ----------
>> 0
>>
>> But when I do this, I get the answer I expect.
>>
>> SQL> SELECT COUNT(*)
>> 2 from leisure_plan_master_temp
>> 3 where membership_class = 'D'
>> 4 AND PAY_METHOD IS NULL;
>>
>> COUNT(*)
>> ----------
>> 11786
>>
>> This isn't exactly correct. There may be other values in this field, and
PRE> if
>> they show up I need to include them, not just records where this field is
>> null. What am I missing? Is it because the value is NULL that Oracle
>> excludes it from the IN() statement, because of the classic definition of
>> NULL (can't be defined, therefore can't be sure it's not a C or a P)?
>>
>> This is easy enough to fix, I'll change my data load to populate the null
>> values with my own code. But still? Have I got the WHY correct?
>>
>> Thanks for any light someone can shed on this stupid question.
>>
>> Lisa Koivu
>> Oracle Database Monkey Mama
>> Fairfield Resorts, Inc.
>> 5259 Coconut Creek Parkway
>> Ft. Lauderdale, FL, USA 33063
>>
>>
>> --
>> Please see the official ORACLE-L FAQ: http://www.orafaq.com
>> --
>> Author: Koivu, Lisa
>> INET: lisa.koivu_at_efairfield.com
>>
>> 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).

-- 
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 - 22:58:24 CDT