RE: Riddle me this.....

Jeremy
P Consider the environment. Please don't print this e-mail unless you really need to.

From: Jared Still [mailto:jkstill_at_gmail.com] Sent: Thursday, April 09, 2009 3:43 PM
To: SHEEHAN, JEREMY
Cc: oracle-l_at_freelists.org
Subject: Re: Riddle me this.....

On Thu, Apr 9, 2009 at 11:47 AM, SHEEHAN, JEREMY <Jeremy.Sheehan_at_fpl.com<mailto:Jeremy.Sheehan_at_fpl.com>> wrote: Ok. Here's an interesting question that some developers threw at me. I don't see anything wrong with the statement but it just does not work....

This statement does not work.....

select * from pjm_nm_sttl_component

   where sttl_id in -- this statement has the 'in'  (select sttl_id_iso from isolink_pjm_sttl_estim_w_iso

    where sttl_id = '131545258'
    )

try this

select count(*)
from isolink_pjm_sttl_estim_w_iso
where sttl_id = '131545258'
and sttl_id_iso is null
/
IIRC the occurrence of NULL in the values returned can lead to 'surprising' results when used with an IN list.

I'm fairly certain Jonathan Lewis and/or Tom Kyte, and no doubt many others have written about this.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l