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 Mon Nov 04 2002 - 16:57:50 CST
