Combining views

From: Ather Mian <athermian_at_yahoo.com>
Date: 3 Dec 2004 11:57:37 -0800
Message-ID: <6ed9e09e.0412031157.3ad4e17c@posting.google.com>

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 Received on Fri Dec 03 2004 - 13:57:37 CST