RE: Riddle me this.....

From: Channa, Santhosh <Santhosh.Channa_at_McKesson.com>
Date: Thu, 9 Apr 2009 14:58:49 -0400
Message-ID: <A1DEC407B8BBA84CA6CA498AA39CF5484414F4_at_NDHV3001.na.corp.mckesson.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of SHEEHAN, JEREMY Sent: Thursday, April 09, 2009 2:47 PM
To: oracle-l_at_freelists.org
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.

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

Received on Thu Apr 09 2009 - 13:58:49 CDT