Re: Riddle me this.....
Date: Thu, 9 Apr 2009 12:42:45 -0700
Message-ID: <bf46380904091242m18d8ab4aue6dc1873efb16623_at_mail.gmail.com>
On Thu, Apr 9, 2009 at 11:47 AM, SHEEHAN, JEREMY <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-lReceived on Thu Apr 09 2009 - 14:42:45 CDT