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

From: Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl>
Date: Tue, 05 Nov 2002 19:04:32 +0100
Message-ID: <vu1gsus36cmqeqn2m4tmuo9767irf50eeb@4ax.com>

On 5 Nov 2002 09:00:08 -0800, thomas.gaines_at_noaa.gov (Thomas Gaines) wrote:

>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

Can not reproduce
Obviously also your dept table differs from the default

SQL*Plus: Release 9.0.1.0.1 - Production on Di Nov 5 18:59:02 2002

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Verbonden met:
Oracle9i Personal Edition Release 9.0.1.1.1 - Production JServer Release 9.0.1.1.1 - Production

SQL> create or replace view dummyview2 as   2 select

  3     distdepts.deptno,
  4     nvl(subquery1.CNT,0) count1,
  5     nvl(subquery2.CNT,0) count2

  6 from
  7 (select distinct deptno from scott.dept) distdepts,   8 (select count(*) CNT, deptno from scott.emp

  9         where scott.emp.sal < 2000
 10         group by deptno) subquery1,
 11    (select count(*) CNT, deptno from scott.emp
 12         where scott.emp.sal < 4000
 13         group by deptno) subquery2

 14 where distdepts.deptno = subquery1.deptno(+)  15 and distdepts.deptno = subquery2.deptno(+);

View is aangemaakt.

SQL> select * from dummyview2
  2 /

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

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

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

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

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

SQL> create table dummytab as select * from dummyview2;

Tabel is aangemaakt.

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

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

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

SQL> select * from dept;

    DEPTNO DNAME LOC
---------- -------------- -------------

        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue Nov 05 2002 - 12:04:32 CST