Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Problem with a view: table from it contains wrong number of rows
Hi,
> what was the count from the view just prior to creating the table?
> are the data in the tables underlying the view static?
> the first place to look is to see who/what might be changing the underlying data.
select count(*) from v_200601 is always the same.
The data in the table underlying the view is static: It's a real, physical, standard table in a standard tablespace. A view (V1) selects from that table, then a new view (V2) selects from that view (i.e. from V1) and then the third view (V_200601) selects from the V2-view (this has been made to keep our application code readable).
Nothing is changing the underlying data, there is no kind of "recursion" or similar.
So basically it's similar to:
create or replace view V1 as (select blah from mytable);
create or replace view V2 as (select blah from V1);
create or replace view v_200601 as (
select a,b,c,sum(d) d from mytable group by a,b,c);
Some computations like sum(pax) are made in the views, and the third view contains a left outer join which aggregates data.
But regardless of how exactly I describe the problem: It all comes down to:
Why is a table created *directly* from a view different from the view contents?
Am I totally brain-dead? I always thought that the two things will always deliver the same results if the underlying data is static (and it *is* static...).
Regards,
Alex
Received on Fri Feb 17 2006 - 14:09:23 CST