Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Bizarre Select count(distinct column) issue

user82929Jun 19 2009 — edited Jun 19 2009
Oracle 10.2.0.3 64bit for Solaris, optimizer features enabled to 10.2.0.3
A client is running SQL that looks like the following
select count(distinct table1id)
from table1,table2
where table1.tableid=table2.tableid; and getting 0 returned.

when they run
select count(*)
from (select distinct table1id)
from table1,table2
where table1.tableid=table2.tableid) they get 10 as the result, which is correct.

we cannot duplicate the issue here on our 10.2.0.4 windows 64 bit database and a select distinct table1id from table1 does not return any null values.

I'm stumped, has anyone come across something like this before? we cannot see their system (security rules) and i can't think of any more revealing queries to see if there is a data issue.
I appreciate any help anyone can give me.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 17 2009
Added on Jun 19 2009
13 comments
900 views