RE: Riddle me this.....

  • From: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
  • To: <Jeremy.Sheehan@xxxxxxx>, "Channa, Santhosh" <Santhosh.Channa@xxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 9 Apr 2009 15:26:50 -0400

Just curious.

When you rewrite it like this:

select  * 
from pjm_nm_sttl_component a, isolink_pjm_sttl_estim_w_iso b
     where  b.sttl_id   =  '131545258'
       and a.sttl_id  = b.sttl_id_iso

what happens?

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of SHEEHAN, JEREMY
Sent: Thursday, April 09, 2009 3:20 PM
To: Channa, Santhosh; oracle-l@xxxxxxxxxxxxx
Subject: RE: Riddle me this.....

I tried this and it still produced the same results with both queries. 

The first returned no rows, the second did.  Thanks for the hint.  

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


-----Original Message-----
From: Channa, Santhosh [mailto:Santhosh.Channa@xxxxxxxxxxxx] 
Sent: Thursday, April 09, 2009 2:59 PM
To: SHEEHAN, JEREMY; oracle-l@xxxxxxxxxxxxx
Subject: RE: Riddle me this.....

My first attempt would be using aliases for the tables and refer the
columns by alias since the column that you are referring in sub query is
common in both the tables.

Regards,
Santhosh Channa

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

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of SHEEHAN, JEREMY
Sent: Thursday, April 09, 2009 2:47 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Riddle me this.....

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'                   
     ) 
     
but this one does:

select  * from pjm_nm_sttl_component 
    where  sttl_id  =  -- this statement has the '='
 (select   sttl_id_iso from isolink_pjm_sttl_estim_w_iso 
     where  sttl_id   =  '131545258'                   
     )


The subquery works.  I can even run the 4 statements.  It pulls back the
same data as the second statement above.  

select  * from pjm_nm_sttl_component 
    where  sttl_id  in (130637378)

select  * from pjm_nm_sttl_component 
    where  sttl_id  in ('130637378')

select  * from pjm_nm_sttl_component 
    where  sttl_id  = 130637378

select  * from pjm_nm_sttl_component 
    where  sttl_id  = '130637378'

We're running 10.2.0.4 on AIX 5.3.7

Any ideas?

Jeremy 
Consider the environment. Please don't print this e-mail unless you
really need to.
--
//www.freelists.org/webpage/oracle-l


zXʃn{i
��i��0���zX���+��n��{�+i�^

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 04-2009