Re: Strange results of aggregate function against view; okay against table

From: Thomas Gaines <thomas.gaines_at_noaa.gov>
Date: 5 Nov 2002 09:00:08 -0800
Message-ID: <fb7f8a4a.0211050900.6bc3f9ff@posting.google.com>

I'm sorry to be difficult, but could someone run the simple testcase below for me, please? It simply makes a view based on the scott.emp and scott.dept tables and then does a query against that view. I've opened a TAR with Oracle Support and the person there says that all is well. But I've seen the following results on three of my databases (these are Oracle8i and Oracle9i on Win2000 and Linux), and I'm stumped!!

This is a snippet of what I'm seeing. The result sets should be equal, although your results may be slightly different from those below if the contents of the emp and dept tables have been modified

++++++++++++++++++++++++++++++++++++++++++++++++ 

SQL> select * from dummyview2;

    DEPTNO COUNT1 COUNT2
---------- ---------- ----------

        10          1          2 
        20          2          5 
        30          5          6 
        40          0          0 
        44          0          0 

SQL>   select deptno, sum(count1), sum(count2) from 
  2       dummyview2 group by deptno; 

    DEPTNO SUM(COUNT1) SUM(COUNT2)
---------- ----------- -----------

        10           2           2 
        20           5           5 
        30           6           6 
        40           0           0 
        44           0           0 

++++++++++++++++++++++++++++++++++++++++++++++++ 

Thanks very much for your time. I'll owe you!

Tom

Thomas Gaines <Thomas.Gaines_at_noaa.gov> wrote in message news:<3DC6FB6E.4FEB54FA_at_noaa.gov>...
> Folks -
>
> I suspect that I may be making a silly mistake, but this is confounding
> my coworker and me. I'm getting some wrong results when performing
> an aggregate function on various columns in a view. The same query
> when executed on a table of the same form and data works as
> expected.
>
> My database version is 8.1.7.2.1. My OS is Win2000.
>
> Here is a simple test case that illustrates my problem. It works
> against
> the scott.emp and scott.dept tables.
>
> =====================================================
>
> -- Create a simple view that combines the emp and dept tables to get
> -- counts displayed in the columns. Be sure to display zeroes when
> -- appropriate.
> create or replace view dummyview2 as
> select
> distdepts.deptno,
> nvl(subquery1.CNT,0) count1,
> nvl(subquery2.CNT,0) count2
> from
> (select distinct deptno from scott.dept) distdepts,
> (select count(*) CNT, deptno from scott.emp
> where scott.emp.sal < 2000
> group by deptno) subquery1,
> (select count(*) CNT, deptno from scott.emp
> where scott.emp.sal < 4000
> group by deptno) subquery2
> where distdepts.deptno = subquery1.deptno(+)
> and distdepts.deptno = subquery2.deptno(+);
>
> -- Display the contents of this view.
> select * from dummyview2;
>
> -- Do a meaningless query that does a simple summation. The
> -- results of this query should look exactly like the stuff in the
> -- view because all of the department ids in the view are unique.
> select deptno, sum(count1), sum(count2) from
> dummyview2 group by deptno;
>
> -- If you've performed the steps above, you'll see that the results
> -- of the query immediately above DO NOT look exactly like the stuff
> -- obtained from the view. They should, though.
>
> -- Do the same query, but this time, do it against
> -- a table with the same form and data as the view.
> drop table dummytab;
> create table dummytab as select * from dummyview2;
> select deptno, sum(count1), sum(count2) from
> dummytab group by deptno;
>
> -- Pretty weird, huh??
>
> =====================================================
>
> Am I doing something silly here? I'm wondering if I'm missing a hint or
>
> perhaps an init.ora parameter that influences the aggregation functions
> when executed against a view.
>
> Thanks for your time.
>
> Tom
>
>
> --
Received on Tue Nov 05 2002 - 11:00:08 CST