RE: Riddle me this.....

  • From: "SHEEHAN, JEREMY" <Jeremy.Sheehan@xxxxxxx>
  • To: Jared Still <jkstill@xxxxxxxxx>
  • Date: Thu, 9 Apr 2009 15:44:24 -0400

This returns a count of 0.

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

From: Jared Still [mailto:jkstill@xxxxxxxxx]
Sent: Thursday, April 09, 2009 3:43 PM
To: SHEEHAN, JEREMY
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Riddle me this.....

On Thu, Apr 9, 2009 at 11:47 AM, SHEEHAN, JEREMY 
<Jeremy.Sheehan@xxxxxxx<mailto:Jeremy.Sheehan@xxxxxxx>> 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

Other related posts: