| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Strange behavior
A good first place to check is that there are no other objects (synonym is a frequent culprit).
try:
select object_name,object_type
from dba_objects
where object_name = 'APPLE';
This will tell you if there is anything else out there with the same name.
HTH,
Mike
"Stephen Andert"
<StephenAndert_at_first To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
health.com> cc:
Sent by: Subject: Strange behavior
ml-errors_at_fatcity.co
m
01/07/2004 02:49 PM
Please respond to
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 ;
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 ;
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-LReceived on Wed Jan 07 2004 - 14:29:25 CST
(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: Michael Boligan INET: michael.boligan.b_at_bayer.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).
 |
 |