Re: Riddle me this.....

From: Jared Still <jkstill@xxxxxxxxx>
To: Jeremy.Sheehan@xxxxxxx
Date: Thu, 9 Apr 2009 12:42:45 -0700

On Thu, Apr 9, 2009 at 11:47 AM, SHEEHAN, JEREMY <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

Follow-Ups:
- RE: Riddle me this.....
  - From: SHEEHAN, JEREMY

References:
- INSERT..VALUES rows processed > executions
  - From: Charlotte Hammond
- Riddle me this.....
  - From: SHEEHAN, JEREMY

Re: Riddle me this.....

Other related posts: