RE: Strange behavior

Mark,

Good idea, but no, the sub-select only returns numbers.

Thanks
Stephen

>>> Mark.Bobak_at_il.proquest.com 01/07/04 01:09PM >>>
Stephen,

Do any of your sub-selects (the queries inside the IN() clause) return NULL? If so, that will definitely cause zero rows to be returned by the
outer query.

-Mark

Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Imagination was given to man to compensate him for what he is not, and
a sense of humor was provided to console him for what he is." --Unknown

-----Original Message-----
Sent: Wednesday, January 07, 2004 2:49 PM To: Multiple recipients of list ORACLE-L

Greetings fellow-DBA-folk:

When I run several queries, I am getting very strange results. Selecting * from apple returns the expected data records, but count(*) does not.
Selecting anything other than * from apple says no records. The subquery for pear works fine on it's own. Selecting anything other than * from apple returns the expected records
when about 900+ values are placed literally in the subquery as shown in
example 4 below.

Am I missing something? Anyone have a direction for me to look for the
cause of this?

Thanks
Stephen

EX 1
select rowid from common.apple
where appleinter1 in (select pearinternal from common.pear where pear_clnt_src_cd = 'CCN')
and appletermd is null
and rownum < 10 ;

no rows selected

EX 2
select rownum from common.apple
where appleinter1 in (select pearinternal from common.pear where pear_clnt_src_cd = 'CCN')
and appletermd is null
and rownum < 10 ;

no rows selected

EX 3
select * from common.apple
where appleinter1 in (select pearinternal from common.pear where pear_clnt_src_cd = 'CCN')
and appletermd is null
and rownum < 10 ;

• 9 rows returned, I removed them for confidentiality reasons ---

EX 4
select count(*) from common.apple
where appleinter1 in ( 7009 , 7010 , 7011 , 7012 , 7013 , 7014 , 7015 ,
7016 , 7017 , 7018 , 7019)
and appletermd is null
and rownum < 10 ;

• 9 rows returned, I removed them for confidentiality reasons ---

EX 5
select count(*) from common.apple
where appleinter1 in (select pearinternal from common.pear where pear_clnt_src_cd = 'CCN')
and appletermd is null;

    COUNT(*)

           0

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: Stephen Andert
  INET: StephenAndert_at_firsthealth.com 

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com 
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: Bobak, Mark
  INET: Mark.Bobak_at_il.proquest.com 

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com 
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephen Andert
  INET: StephenAndert_at_firsthealth.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).