Re: View of a union of tables?

I haven't tried it, but (version 7.3.3 upwards) you may be able to make effective use through partitioned views - using WHERE CLAUSE
partitioning rather than constraint-based partitioning.

There is a note on my web-site about partitioned views, but only constraint-based. But for WHERE CLAUSE in this case you MAY be able to get away with:

create view full_view as
select * from view_referencing_sybase_table1 where {some condition true only on table 1} UNION ALL
select * from view_referencing_sybase_table2 where {some condition true only on table 2} UNION ALL
 . . .
UNION ALL
select * from view_referencing_sybase_tableN where {some condition true only on table N}

It is possible that a query of the form

    select ... from full_view
    where {condition that matches tableX}

will only select data from the one Sybase table.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

dpurrington_at_my-deja.com wrote in message <80uf35$tfl$1_at_nnrp1.deja.com>...
>I know this is going to sound a little freaky, but I'd like to use a
>view of a union of tables to simplify the perspective of reporting
>users.
>
>Basically, there are tables in another database (Sybase) and I want to
>use Oracle Gateway so that the users have an Oracle interface. In the
>Sybase database, the tables are partitioned by using multiple identical
>databases (basically schema to us Oracle folk). I don't want users to
>have to deal with those multiple tables, I want to provide a
>consolidated interface. So, if table_x exists in multiple databases in
>Sybase, I want one Oracle view as an access point to those tables.
>
>I was thinking I could use a view with a union of all the tables in the
>query. Will this work? I don't want the database to query all the
>tables for every query on the view. That's my goal.
>
>I also thought about maybe a view of a union of views, but that might
>not necessarily get me anywhere better.
>
>Any help would be appreciated!
>
>Dave
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Wed Nov 17 1999 - 16:03:45 CST