Mark, Good idea, but no, the sub-select only returns numbers.
Thanks Stephen >>> [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).