Re: Combining views

Ather Mian wrote:

> Hi,
>
> I am working with Oracle 9i in an ERP environment. I am pulling the
> sales data by a view. My issue is that we have 12 companies therefore
> I have to repeat the selects 12 times to get the data I need. Since
> each company has it own order header and order detail tables.
>
> Presently this is what I do.
>
> create or replace view Daily_Sales as
> Company 1 Select
> Union all
> Company 2 Select
> Union all
> Company 3 Select
> ...........
>
> My knowledge of oracle is very rudimentary but if you can please give
> me some pointers then I can have someone help me with it.
>
> I am sure there is an elegant way to do this then what I am doing.
>
> I have been reading about 9i datawarehouse features like Materialized
> views is that something I can use to do this.
>
> This is one of my many things that I do. Unfortunately I use the same
> technique by union all the selects together.
>
> Any help would be greatley appreciated.
>
> Regards
>
> Ather

The only elegant solution would be to normalize. Put all of the data into a single table and add a column for company_id.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)