Re: Help with SQL query (and outer joins...)

Go back to the original tables and write a single view. Views built on views do not perform well.

Daniel Morgan

Steven Garcia wrote:

> I'm having trouble with a view I'm developing. For the simplified
> version...I have two views that look like:
>
> View 1:
> Logday (date, 'ddd') totalcount
> --------------------- -----------
> 20-FEB-2002 10
> 21-FEB-2002 20
>
> View 2:
> Logday (date, 'ddd') revenue
> --------------------- ----------
> 21-FEB-2002 5.00
> 22-FEB-2002 8.00
>
> I want to create an uber view from both of these views that it will look
> like
> Logday (date, 'ddd') totalcount revenue
> --------------------- ---------- --------
> 20-FEB-2002 10 0
> 21-FEB-2002 20 5.00
> 22-FEB-2002 0 8.00
>
> I know this is a perfect candidate for an outer join. The real example I'm
> trying to do is more complicated because there are 4 views (each with a
> logday column) and I want to create the uber view that has a column from
> each of the 4 views, along with some other data that will exist across all
> four views (so there won't be a need to do a outer-join on those columns.)
>
> How do I construct such a query? I'm trying to solve my problem in such a
> way that my uber view will look like
> the following...
>
> Logday col1 col2 col3
> col4
> ------------------- ---------- ---------- ---------- ------------
> 20-FEB-2002 0 1 15 0
> 21-FEB-2002 20 10 0 23
> 22-FEB-2002 14 0 14 0
> 23-FEB-2002 0 4 0
> 34
>
> where I have 0 in the columns where the data doesn't exist from my "feeder"
> views.
>
> We are currently solving this problem by having a job on a daily basis do
> this population in a table, but our business requirements have changed such
> that the current implementation is not suitable. I'm a little worried about
> performance of this view but these reporting tables are not access often in
> our application (so a performance hit is something that I will accept.)
>
> Thanks for your help,
> Steve Garcia
Received on Fri Mar 29 2002 - 14:37:39 CST